Does MySQL record how often it uses indices?

Question

I've got a table (InnoDB) with a fair number of indices. It would be great to know if one (or more) of these was never actually used. I don't care as much about the disk space, but insertion speed is sometimes an issue.

Does MySQL record any statistics on how often it has used each index when running queries?

Answer 1

AFAIK, it only records total count of index usages.

SHOW STATUS; has the Select_scan column.

Answer 2

There is a way to find unused indexes with a small patch to MySQL.

Answer 3

I'd suggest turning on profiling and doing some bottleneck analysis.

set profiling=1;

After which point you can let some of your heavy queries run for awhile. Eventually, you turn it off and than examine the queries that ran to see which were heaviest in execution time. If you don't see any

Some other commands to note are:

show profiles;

select sum(duration) from information_schema.profiling where query_id=<ID you want to look at>;

show profile for query <you want to look at>;

Finally if you wish to see unused indexes and you have userstats enabled

SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME
FROM information_schema.statistics `s` LEFT JOIN information_schema.index_statistics INDXS
ON (s.TABLE_SCHEMA = INDXS.TABLE_SCHEMA AND
s.TABLE_NAME=INDXS.TABLE_NAME AND
s.INDEX_NAME=INDXS.INDEX_NAME)
WHERE INDXS.TABLE_SCHEMA IS NULL; 

Answer 4

The plans for queries don't vary much - turn on the (slow) query logging with a threshold of 0 seconds, then write a bit of code in the language of your choice to parse the log files and strip all the literals out the queries, then do explain plans for any query executed more then 'N' times.

C.