Bug #40575 Index usage statistics

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