1. Indeed you are right, I used the “ENGINE IS NOT NULL” to get rid of the views. I was considering reading the TABLE_TYPE, but decided to go for the NOT NULL. There are many column in the INFORMATION_SCHEMA whose values I don’t feel comfortable with (values like ‘YES’ and ‘NO’, instead of 1, 0 etc.)

2. Again, you are right. In this post I did not consider FULLTEXT and HASH keys at all. I don’t get to cross them much; I’ll review the queries.

Thanks for the reference to your Redundant Index Finder. I’ll take a closer look later on. Meanwhile it looks very interesting.

Regards,
Shlomi

Few comments though….

In the table size query you do:

WHERE TABLE_SCHEMA NOT IN (‘mysql’, ‘INFORMATION_SCHEMA’)
AND ENGINE IS NOT NULL

I am assuming you added ENGINE IS NOT NULL to get rid of views. Personally, I feel the WHERE clause would be better if written like so:

WHERE TABLE_TYPE = ‘BASE TABLE’

This gets rid of all information schema tables (since those are all ‘SYSTEM VIEW’s). It does leave the tables in the mysql database in the result, but I consider that an advantage.

Second, about your redundant indexes query:

“See if some index is a prefix of another (in which case it is redundant):”

This is not completely true. That is, you need to take the indexing algorithm in account too. If I have

create table books (
title varchar(50)
, description varchar(255)
, index(title)
, fulltext index(title, description)
)
engine = MyISAM

then the index on title will be a BTREE index which has radicaly different properties than the FULLTEXT index. So I would certainly not claim the index on title is redundant in this case.

Another, interesting, example is when working with MEMORY tables. For MEMORY tables we can have BTREE and HASH indexes. Again, like we saw with FULLTEXT we should take the algorithm into account when comparing indexes. But another, more subtle thing, comes into play in this case too.

If I have a HASH index on columns (A,B) and another HASH index on columns (B,A) then, according to your arguments, we should not consider either of these indexes redundant as they are not a prefix of each another. However, for HASH indexes, the order of the columns does not matter at all – HASH index can be used only if a value is known for all columns in the HASH index – the HASH function is applied to all column positions to compute a HASH code that is used to do the lookup.

So a HASH index is redundant if there is another HASH index that has the same set of columns *in any order*.

Here on mysqlforge you can find some queries on the information schema that i feel do a better job:

http://forge.mysql.com/tools/tool.php?id=45

kind regards,

Roland Bouman