Bug #40575 Index usage statistics
Submitted: 7 Nov 2008 12:47 Modified: 25 May 2012 10:04
Reporter: Oli Sennhauser Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D5 (Feature request)

[7 Nov 2008 12:47] Oli Sennhauser
Description:
MySQL users often create indexes without verifying if they are used or not. This causes unnecessary memory consumption and slows down DML.

How to repeat:
Ask Support or Consultants :)

Suggested fix:
It would be great having an I_S table which provides statistics about index usage.

For example:

mysql> select * from INFORMATION_SCHEMA.STATISTICS;
+--------------+---------------------------+------------+-----------+-----------+
| INDEX_SCHEMA | INDEX_TABLE               | INDEX_NAME | UPDATES   | READS     |
+--------------+---------------------------+------------+-----------+-----------+
| test         | test_table                | PRIMARY    | 123456789 | 123456789 |
| test         | test_table                | email_adr  |       456 |    123456 |
| test         | test_table                | size       | 123456789 |         0 |
+--------------+---------------------------+------------+-----------+-----------+

So one could easily see, that an index is never used.

I think it should not be too difficult to implement that around the handler interface.

It would even be greater if we could have all these status on a per table level (with timing???):

| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 12    |
| Handler_read_key           | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 167   |
| Handler_rollback           | 1     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 137   |
[7 Nov 2008 18:10] Valeriy Kravchuk
Thank you for a reasonable feature request. Will something like described here:

http://www.mysqlperformanceblog.com/2008/09/12/unused-indexes-by-single-query/

help you?
[10 Nov 2008 14:16] Oli Sennhauser
Hi Valeriy,

That is probably in the way I was looking for. If we could have such patches in our Mainline code, it would be great.

Oli
[8 Dec 2008 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[21 Dec 2011 6:47] vipin kumar
how to select warning and critical limit for index usase in opsview while using check_mysql_health...