Version 0.04 of the MySQL Index Analyzer has just been uploaded. This is the first version that features a Swing GUI and actually provides real analysis functions and the generation of ALTER TABLE statements to get rid of superfluous indices.
A quick overview of what is new:
- Swing GUI
- Analysis features as on the command line
- Copying of generated ALTER TABLE statements to the clipboard
- Information on data and index size distribution
- Rudimentary analysis of possible disk space savings
Especially the size analysis is in a very early stage and will probably be way off most of the time. I do however plan to spend more work there, because I think this is a very important point.
Documentation in the distribution file is somewhat outdated, but I will give some more details here. The following example is based on a simple database with only one table called
people. The table consists of four columns:
CREATE TABLE `people` ( `id` int(11) NOT NULL auto_increment, `firstname` varchar(32) default NULL, `lastname` varchar(32) default NULL, `birthday` date default NULL, PRIMARY KEY (`id`), KEY `idx_reversename` (`lastname`,`firstname`), KEY `idx_id_birth_first` (`id`,`birthday`,`firstname`), KEY `idx_full` (`firstname`,`lastname`), KEY `idx_first` (`firstname`), KEY `idx_id_first` (`id`,`firstname`), KEY `idx_id_birth` (`id`,`birthday`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
There are 7 indices defined on that table, including the PRIMARY key.
After login, the analysis starts immediately. I will implement a more fine grained control later, for now, the whole schema is analyzed:
Thanks to the great folks of swingfx.dev.java.net waiting is done with some style ;)
As soon as the analysis is ready, you will see a screen like this:
In the upper part of the Window you see some general information about data and index sizes. The Index/Data Ration of 3.0 means, that indices for this database need 3 times the amount of space the real data eats up. This ratio is also displayed as a bar. Please be aware, that all numbers only refer to the tables shown in the details view below, the database might be a lot bigger!
The lower part shows a hierarchical view of the database structure. Tables are shown as little folders, detected index obsoletions are the leafs below them. In the example you can see, that 2 indices where found (
idx_first, idx_id_birth) which can be safely removed, because they are only prefixes of
idx_id_birth_first. For each obsoletion and estimated amount of disk space savings is displayed that would result from dropping that particular index. On the table level you see the sum of all obsoleted indices savings.
Please let me know I you find this tool helpful, have suggestions on what to improve/include, and of course, if you'd like to participate in the development.