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:

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