Log in

No account? Create an account
entries friends calendar profile My Website Previous Previous Next Next
Mark Atwood
SQL. Find probably useless indexes.
A useful SQL query I worked out yesterday.

It finds all the indexes in the database "my_database" that have a cardinality/count ratio of less than 0.1%

Such indexes are probably not very useful, and should be looked at carefully to justify their existence.

use information_schema;

select tables.table_name, statistics.index_name, statistics.cardinality, tables.table_rows

  from tables

  join statistics

  on (statistics.table_name = tables.table_name

   and statistics.table_schema = 'my_database'

   and ((tables.table_rows / statistics.cardinality) > 1000));

I worked this out on my current MySQL PS gig. The client has indexes on nearly every column of nearly every table. For example, all of their customers are in Texas, but they still put an index on the "state" column in the address table.

Current Location: InfoMart Building, Dallas TX

3 comments or Leave a comment
From: hollyking Date: January 29th, 2008 08:48 pm (UTC) (Link)
Thanks for sharing this. I don't have much call for it at the moment but I'm putting it in the tool box for the future.
From: dburns Date: January 30th, 2008 02:19 am (UTC) (Link)

Small bug

If you have multiple databases running on the same MySQL instance that have the same schema, you will get a cartesian product from the query as you wrote it. I believe you need to add one more join condition:

and tables.table_schema=statistics.table_schema

From: dburns Date: January 30th, 2008 01:51 pm (UTC) (Link)

Re: Small bug

And one more small bug: experimenting further, it looks like FULLTEXT indices always have a cardinality of 1 (which seems odd given I have one indexed column with about 2M unique values). So, I added another condition to prune down the result set:

and statistics.index_type != 'FULLTEXT';
3 comments or Leave a comment