Redundant Index Analyzer

Indexes in a database are double-sided: They are required for SELECT queries on large tables to be fast and efficient, but on the other side whenever data in a table changes (ie. when executing INSERT, UPDATE and DELETE statements) the server will have to maintain the index what is an expense (it causes IO and uses CPU). So, you should have what indexes you need and what indexes are used by the server and you should avoid indexes that the server will not use anyway and indexes that have no more information than what another index already has. A typical example is that if you have 'index1' defined on (column1, column2) and 'index2' on (column1, column2, column3) index1 is redundant (note that this statement gets modified if one or more of the indexes are UNIQUE or FULL-TEXT indexes).

Also redundant indexes consume disk space and occupy memory, what is one more reason to avoid them.

The feature to find redundant indexes analyses index information stored in the Information_Schema database and finds indexes on tables that are redundant. It does by executing a complex query and analyzing the result client-side. The control for activating this as well as the result will display in INFO tab in HTML-mode for both the database level and the table level.

Database-level: You can toggle the column displaying redundant indexes ON and OFF by clicking the button SHOW REDUNDANT INDEXES/HIDE REDUNDANT INDEX INFO (the caption of the button will change). Also note that if you click a table name the table will get focus in the Object Browser and the Object Browser will expand the folders containing the table symbol if it is not already.

Table-level: Also here You can toggle the column displaying redundant indexes ON and OFF by clicking the button. Redundant indexes will be marked with a red background color.

However, note that there may be some useless indexes not found by this feature. SELECT-performance when querying a very small table (the classical example is a table with the name of all US states - and an even more trivial example is a table with a single row) will not improve with an index. An index on such table is useless - but not 'redundant' and it will not be found by the 'Find Redundant Indexes' feature as we do not query the table itself - only index information from Information_Schema. For the same reason this feature requires a 5.0x server or higher to be functional.

Note:This feature is available in SQLyog Ultimate.