----------------------------------------------------------------------- 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: -=-=(Shinguz - Wed, 10 Mar 2010, 11:30)=-=- High Level Description modified. --- /tmp/wklog.103.old.30875 2010-03-10 11:30:45.000000000 +0000 +++ /tmp/wklog.103.new.30875 2010-03-10 11:30:45.000000000 +0000 @@ -35,3 +35,6 @@ 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. + +If some more specification is need for implementation let me know and I will +collect the stuff from the sources mentioned above. 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. If some more specification is need for implementation let me know and I will collect the stuff from the sources mentioned above. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)