Percona Server feature request - index hints
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS |
Triaged
|
Wishlist
|
Unassigned |
Bug Description
We have many unused indexes. At least, we *think* they are unused. The problem is that the tables are large and the code is vast and has a number of index hints buried in obscure corners. It would be awesome if we could mark an index as "invisible" or something as far as the optimizer is concerned but such that we could get warnings in the logs perhaps if we attempt to hit is explicitly that way we can more easily track whether it can really be removed. I know I could just track things via the various percona information_schema statistics tables. However, I'm not quite adventurous enough to pull the trigger in some cases.
Otherwise, can the parser / optimizer be made to not fail a query if index hints point to a non-existent index? Maybe it could just drop a note in the log and / or issue a warning.
If I understand you correctly, you are looking for:
- a way to annotate a given index as disabled, without removing it;
- then the query optimizer producing a warning when a disabled index is forced.
OR,
- make an index hint containing invalid index name warning, not error.
Since your goal is to get rid of unused indexes and unnecessary index hints that might be forcing their use, I'd consider other ways to achieve this:
- pt-index-usage to get rid of indexes that are already unused;
- pt-query-digest to find queries with index hints; remove the hint on a test db, evaluate, repeat.