MySQL Bugs: #40575: Index usage statistics

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

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   |

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?

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

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".

how to select warning and critical limit for index usase in opsview while using check_mysql_health...