While the index merge access types certainly are useful
for a number of queries, there has been some frustration
expressed both from customers and the community about how
it...
- is not used when it should have been
- is used when ref access is obviously better
- merges suboptimal indexes
- is too restricted in which conditions can be used
I could come up with numerous examples of related bugs and
feature requests dating back more than six years. To list a few:
17673, 30151, 23322, 65274, 65359.
The good news is that we have fixed all these issues in the
latest released MySQL 5.6 server.
Consider the problem in BUG#65274 (simplified a bit):
CREATE TABLE phpbb_posts (
...
KEY `topic_id` (`topic_id`),
KEY `forum_id` (`forum_id`)
KEY `tid_fid` (`topic_id`,`forum_id`),
);
SELECT count(*) FROM phpbb_posts WHERE topic_id = 110126 AND
forum_id = 19;
Read more »