User statistics: track USE/FORCE/IGNORE INDEX

[Federico]

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.

[Sveta Smirnova (svetasmirnova)]

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.

[Federico (federico-raz)]

Thanks Sveta,

True, but I think this doesn't help much in the case I mentioned. In my case all duplicate indexes were used, but still most of them could be dropped without damages. In a few cases however there was a FORCE INDEX or IGNORE INDEX, so after the drop, some queries started to fail.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2511