MySQL Bugs: #40575: Index usage statistics

Bug #40575	Index usage statistics
Submitted:	7 Nov 2008 13:47	Modified:	7 Dec 2008 19:10
Reporter:	Oli Sennhauser
Status:	No Feedback
Category:	Server	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		Target Version:

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