Samstag, September 23, 2006

MySQL Index Analyzer: 0.04 released

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.

Now let's see what the MySQLIndexAnalyzer has to say:

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_full and 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.

Via the "Generate DML" button you get to this dialog:

From here you can either save the DML (ALTER TABLE) statements to a file or store them to the system clipboard: (Please do not blindly apply them to your database!)

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.

Kommentare:

nhm tanveer hossain khan (hasan) hat gesagt…

very interesting, i am going to give a try ...
thank you for such great tool...

Anonym hat gesagt…

Great tool!!!!