Tuesday, October 2, 2012

Index merge annoyances fixed in MySQL 5.6

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...
  1. is not used when it should have been
  2. is used when ref access is obviously better
  3. merges suboptimal indexes
  4. 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;