Mark Atwood (fallenpegasus) wrote,
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.
Tags: mysql

  • Post a new comment


    Comments allowed for friends only

    Anonymous comments are disabled in this journal

    default userpic

    Your reply will be screened

    Your IP address will be recorded