User statistics: track USE/FORCE/IGNORE INDEX
Bug #1733006 reported by
Federico
This bug affects 1 person
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.5 |
Won't Fix
|
Wishlist
|
Unassigned | |||
5.6 |
Triaged
|
Wishlist
|
Unassigned | |||
5.7 |
Triaged
|
Wishlist
|
Unassigned |
Bug Description
If a non-existing index is mentioned in a USE/FORCE/IGNORE INDEX clause, the query will fail with an error. This makes extremely dangerous to drop an index, even if we know that it is duplicate, or has a low cardinality. Even for a consultant it is dangerous to suggest to drop an index.
Please consider adding new columns in INDEX_STATISTICS table, which count how many times the index has been explicitally USEd, FORCEd or IGNOREd.
To post a comment you must log in.
Thank you for the reasonable feature request.
However you can already know how many times index was used if use Performance Schema:
mysql> select object_schema, object_name, index_name, count_fetch, count_insert, count_delete, count_update from performance_ schema. table_io_ waits_summary_ by_index_ usage where object_ schema= 'test'; ------- --+---- ------- --+---- ------- -+----- ------- -+----- ------- --+---- ------- ---+--- ------- ----+ ------- --+---- ------- --+---- ------- -+----- ------- -+----- ------- --+---- ------- ---+--- ------- ----+ ------- --+---- ------- --+---- ------- -+----- ------- -+----- ------- --+---- ------- ---+--- ------- ----+
+------
| object_schema | object_name | index_name | count_fetch | count_insert | count_delete | count_update |
+------
| test | t1 | i_idx | 1 | 0 | 0 | 0 |
| test | t1 | j_idx | 0 | 0 | 0 | 0 |
| test | t1 | k_idx | 0 | 0 | 0 | 0 |
| test | t1 | NULL | 3 | 3 | 0 | 0 |
+------
4 rows in set (0,01 sec)
It won't count ignored indexes.