How to tell which indices are NOT helping

Hi –

I have a database with a plethora of inherited indices.

Is there a systematic way to tell whether an index is getting used, other than replaying “EXPLAIN” statements and using a process of elimination?

I understand how to tell, on any given query, the set of indices used. I’m hoping that there is a more systematic answer – e.g. given all queries executed by this server since some point in time, how often is a specific index used.

Thanks,
Mike

http://www.percona.com/docs/wiki/release:5075-b12 , userstatsv2.patch

This patch should be usefull.

Januzi –

Thanks very much for the pointer – sounds perfect if I can afford the time/risk to deploy a patched server.

I’d appreciate any ideas that don’t require this patch.

Thanks.

http://hackmysql.com/mysqlidxchk