----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Index usage tracker CREATION DATE..: Wed, 10 Mar 2010, 11:29 SUPERVISOR.....: Bothorsen IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Client-BackLog TASK ID........: 103 (http://askmonty.org/worklog/?tid=103) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: What indexes are needed is often easy to find. What is more difficult is to find which indexes are not used at all. Thus some statistics about the index usage would be nice. I think Percona has already something like this done some time ago. And big O has similar functionality. I could imagine something like this: +------------+------------+--------------+---------------------+---------------------+---------------------+------------+----------+-----------+-----------+------------+ | table_name | index_name | index_length | create_time | update_time | use_time | read_first | read_key | read_next | full_scan | range_scan | +------------+------------+--------------+---------------------+---------------------+---------------------+------------+----------+-----------+-----------+------------+ | test_table | idx1 | 1234567890 | 2010-01-01 00:00:00 | 2010-01-01 00:00:00 | 2010-03-10 11:34:56 | 1234 | 42560 | 2468 | 234 | 321 | | test_table | idx2 | 123456 | 2010-01-01 00:00:00 | NULL | NULL | 0 | 0 | 0 | 0 | 0 | | test_table | idx3 | 234561 | 2010-01-01 00:00:00 | 2010-03-10 11:12:34 | 2010-03-10 11:34:56 | 7890 | 89890 | 15780 | 678 | 321 | | test_table | idx4 | 345612 | 2010-01-01 00:00:00 | 2010-03-10 11:34:56 | NULL | 0 | 0 | 0 | 0 | 0 | | test_table | idx5 | 456123 | 2010-01-01 00:00:00 | 2010-03-10 06:56:12 | NULL | 0 | 0 | 0 | 0 | 0 | | test_table | idx6 | 561234 | 2010-01-01 00:00:00 | 2010-03-10 01:12:34 | 2010-03-10 11:34:42 | 3456 | 12356 | 6912 | 123 | 12 | +------------+------------+--------------+---------------------+---------------------+---------------------+------------+----------+-----------+-----------+------------+ Possibly this could/should be implemented on the handler interface level because there we know what we are touching? But I am not familiar with the code. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)