Description:
1. Database has FK indexes which have very poor selectivity, so idea of those indexes is not being used in plan creation, just keep data integrity.
2. Sometimes on different tables and different queries optimizer starts producing "wrong" plan and ignores very good existing index and chooses index_merge with the indexes which are useless for search.
Usually it gets resolved after some time, maybe because of ANALYZE/OPTIMIZE TABLE called, maybe because of other queries impact.
How to repeat:
Unfortunately still have no reproducible steps as it is very uneasy to reproduce.
But this happens on live business system so we do not have plan/time to experiment and find out exact conditions. If we have more precise info we will update this ticket.
Possible explanations why 2. can happen:
maybe that because of Bug #36513,
maybe just because of:
a. Cardinality in InnoDB is calculated approximately,
b. If table is heavily modified, daily OPTIMIZE TABLE is required.
As result it is very likely that "with some time" cardinality will be calculated incorrectly and Optimizer will have good background to choose wrong index
This is feature request without reproduction steps but please assign high priority because it has high impact on business.
Suggested fix:
One of possible fix which will satisfy described system is introducing global flag which will force optimizer to ignore index_merge optimization (in joins?).
Otherwise we can introduce flag for Index which will force optimizer to ignore it in plan creation because that index is intended only for FK integrity.
Using FORCE and IGNORE index is not an option because problem appears in different queries and different tables.
