maria-developers team mailing list archive

[worklog-noreply@xxxxxxxxxxxx]

-----------------------------------------------------------------------
                              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)