MySQL Bugs: #65274: MySQL misplanning query with index

Bug #65274	MySQL misplanning query with index_merge
Submitted:	10 May 2012 5:53	Modified:	3 Aug 2012 15:44
Reporter:	Max Barry	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1., 5.5., 5.6.6	OS:	Linux (Ubuntu Server 12.04)
Assigned to:		CPU Architecture:	Any
Tags:	index_merge

Description:
Hello,

I was advised to report this by a phpBB3 developer (see: http://www.phpbb.com/community/viewtopic.php?f=46&t=2153923&p=13145327).

Essentially, MySQL is performing an index_merge when (apparently) it has no good reason to do so. The phpBB developer said, "It sounds like mysql is misplanning that query for some reason. Given an index on both forum_id and topic_id I cannot imagine a situation where it would be advantageous to do an "index merge" over the separate forum_id and topic_id indexes instead of using the index covering both columns."

Here is the relevant query:

mysql> EXPLAIN SELECT post_id, topic_id, forum_id FROM phpbb_posts WHERE topic_id = 110126 AND forum_id = 19 ORDER BY post_time ASC LIMIT 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: phpbb_posts
         type: index_merge
possible_keys: topic_id,tid_post_time,tid_fid,fid_tid_post_time,forum_id
          key: topic_id,forum_id
      key_len: 3,3
          ref: NULL
         rows: 1592
        Extra: Using intersect(topic_id,forum_id); Using where; Using filesort
1 row in set (0.29 sec)

The index that the phpBB developer thinks MySQL should be using is 'tid_fid'.

Here are the indexes:

mysql> SHOW INDEXES FROM phpbb_posts \G
*************************** 1. row ***************************
        Table: phpbb_posts
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: post_id
    Collation: A
  Cardinality: 7461329
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: phpbb_posts
   Non_unique: 1
     Key_name: topic_id
 Seq_in_index: 1
  Column_name: topic_id
    Collation: A
  Cardinality: 152272
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: phpbb_posts
   Non_unique: 1
     Key_name: poster_ip
 Seq_in_index: 1
  Column_name: poster_ip
    Collation: A
  Cardinality: 298453
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 4. row ***************************
        Table: phpbb_posts
   Non_unique: 1
     Key_name: poster_id
 Seq_in_index: 1
  Column_name: poster_id
    Collation: A
  Cardinality: 64321
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 5. row ***************************
        Table: phpbb_posts
   Non_unique: 1
     Key_name: post_approved
 Seq_in_index: 1
  Column_name: post_approved
    Collation: A
  Cardinality: 1
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 6. row ***************************
        Table: phpbb_posts
   Non_unique: 1
     Key_name: tid_post_time
 Seq_in_index: 1
  Column_name: topic_id
    Collation: A
  Cardinality: 152272
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 7. row ***************************
        Table: phpbb_posts
   Non_unique: 1
     Key_name: tid_post_time
 Seq_in_index: 2
  Column_name: post_time
    Collation: A
  Cardinality: 7461329
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 8. row ***************************
        Table: phpbb_posts
   Non_unique: 1
     Key_name: post_username
 Seq_in_index: 1
  Column_name: post_username
    Collation: A
  Cardinality: 1
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 9. row ***************************
        Table: phpbb_posts
   Non_unique: 1
     Key_name: tid_fid
 Seq_in_index: 1
  Column_name: topic_id
    Collation: A
  Cardinality: 152272
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 10. row ***************************
        Table: phpbb_posts
   Non_unique: 1
     Key_name: tid_fid
 Seq_in_index: 2
  Column_name: forum_id
    Collation: A
  Cardinality: 152272
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 11. row ***************************
        Table: phpbb_posts
   Non_unique: 1
     Key_name: fid_tid_post_time
 Seq_in_index: 1
  Column_name: forum_id
    Collation: A
  Cardinality: 23
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 12. row ***************************
        Table: phpbb_posts
   Non_unique: 1
     Key_name: fid_tid_post_time
 Seq_in_index: 2
  Column_name: topic_id
    Collation: A
  Cardinality: 152272
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 13. row ***************************
        Table: phpbb_posts
   Non_unique: 1
     Key_name: fid_tid_post_time
 Seq_in_index: 3
  Column_name: post_time
    Collation: A
  Cardinality: 7461329
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 14. row ***************************
        Table: phpbb_posts
   Non_unique: 1
     Key_name: forum_id
 Seq_in_index: 1
  Column_name: forum_id
    Collation: A
  Cardinality: 23
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
14 rows in set (0.00 sec)

And here is the table:

mysql> SHOW TABLE STATUS WHERE name = 'phpbb_posts' \G
*************************** 1. row ***************************
           Name: phpbb_posts
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 7461329
 Avg_row_length: 853
    Data_length: 6371126872
Max_data_length: 281474976710655
   Index_length: 709460992
      Data_free: 0
 Auto_increment: 9189247
    Create_time: 2012-05-06 00:01:10
    Update_time: 2012-05-06 00:02:53
     Check_time: 2012-05-06 00:09:06
      Collation: utf8_bin
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

How to repeat:
I don't think this occurs on small databases, so I can't enclose reproducibility instructions in the space provided. MySQL's output to EXPLAIN differs depending on the exact topic_id and forum_id in question.

It would be nice to know the exact output of SHOW CREATE TABLE and SHOW TABLE STATUS for the table used, and the output of:

SELECT count(*) FROM phpbb_posts WHERE topic_id = 110126 AND forum_id = 19;
SELECT count(*) FROM phpbb_posts WHERE topic_id = 110126;
SELECT count(*) FROM phpbb_posts WHERE forum_id = 19;

for values where supposedly wrong execution path is used. 

This can be a duplicate of Bug #32254, for example.

On 10 May 14:41 Valeriy Kravchuk wrote:
>
> It would be nice to know the exact output of SHOW CREATE TABLE and
> SHOW TABLE STATUS for the table used, and the output of:
> 
> SELECT count(*) FROM phpbb_posts WHERE topic_id = 110126 AND forum_id = 19;
> SELECT count(*) FROM phpbb_posts WHERE topic_id = 110126;
> SELECT count(*) FROM phpbb_posts WHERE forum_id = 19;
>
> for values where supposedly wrong execution path is used. 

Thanks for the response! Sorry for not including that table info earlier.

mysql> SHOW CREATE TABLE phpbb_posts \G
*************************** 1. row ***************************
       Table: phpbb_posts
Create Table: CREATE TABLE `phpbb_posts` (
  `post_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `topic_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `forum_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `poster_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `icon_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `poster_ip` varchar(40) COLLATE utf8_bin NOT NULL DEFAULT '',
  `post_time` int(11) unsigned NOT NULL DEFAULT '0',
  `post_approved` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `post_reported` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `enable_bbcode` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `enable_smilies` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `enable_magic_url` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `enable_sig` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `post_username` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `post_subject` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `post_text` mediumtext COLLATE utf8_bin NOT NULL,
  `post_checksum` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `post_attachment` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `bbcode_bitfield` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `bbcode_uid` varchar(8) COLLATE utf8_bin NOT NULL DEFAULT '',
  `post_postcount` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `post_edit_time` int(11) unsigned NOT NULL DEFAULT '0',
  `post_edit_reason` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `post_edit_user` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `post_edit_count` smallint(4) unsigned NOT NULL DEFAULT '0',
  `post_edit_locked` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`post_id`),
  KEY `topic_id` (`topic_id`),
  KEY `poster_ip` (`poster_ip`),
  KEY `poster_id` (`poster_id`),
  KEY `post_approved` (`post_approved`),
  KEY `tid_post_time` (`topic_id`,`post_time`),
  KEY `post_username` (`post_username`),
  KEY `tid_fid` (`topic_id`,`forum_id`),
  KEY `fid_tid_post_time` (`forum_id`,`topic_id`,`post_time`),
  KEY `forum_id` (`forum_id`)
) ENGINE=MyISAM AUTO_INCREMENT=9189247 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS WHERE name = 'phpbb_posts' \G
*************************** 1. row ***************************
           Name: phpbb_posts
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 7461329
 Avg_row_length: 853
    Data_length: 6371126872
Max_data_length: 281474976710655
   Index_length: 709460992
      Data_free: 0
 Auto_increment: 9189247
    Create_time: 2012-05-06 00:01:10
    Update_time: 2012-05-06 00:02:53
     Check_time: 2012-05-06 00:09:06
      Collation: utf8_bin
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

mysql> SELECT count(*) FROM phpbb_posts WHERE topic_id = 110126 AND forum_id = 19;
+----------+
| count(*) |
+----------+
|    46651 |
+----------+
1 row in set (2 min 8.98 sec)

mysql> SELECT count(*) FROM phpbb_posts WHERE topic_id = 110126;
+----------+
| count(*) |
+----------+
|    46651 |
+----------+
1 row in set (0.02 sec)

mysql> SELECT count(*) FROM phpbb_posts WHERE forum_id = 19;
+----------+
| count(*) |
+----------+
|   264808 |
+----------+
1 row in set (0.34 sec)

And just in case it's useful, EXPLAIN statements for the above:

mysql> EXPLAIN SELECT count(*) FROM phpbb_posts WHERE topic_id = 110126 AND forum_id = 19 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: phpbb_posts
         type: index_merge
possible_keys: topic_id,tid_post_time,tid_fid,fid_tid_post_time,forum_id
          key: topic_id,forum_id
      key_len: 3,3
          ref: NULL
         rows: 1592
        Extra: Using intersect(topic_id,forum_id); Using where; Using index
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT count(*) FROM phpbb_posts WHERE topic_id = 110126 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: phpbb_posts
         type: ref
possible_keys: topic_id,tid_post_time,tid_fid
          key: topic_id
      key_len: 3
          ref: const
         rows: 45746
        Extra: Using index
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT count(*) FROM phpbb_posts WHERE forum_id = 19 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: phpbb_posts
         type: ref
possible_keys: fid_tid_post_time,forum_id
          key: fid_tid_post_time
      key_len: 3
          ref: const
         rows: 161103
        Extra: Using index
1 row in set (0.00 sec)

Thank you for the feedback.

For me output of SELECT COUNT(*) confirms this is duplicate of bug #32254. Only thing which confuses me is:

mysql> EXPLAIN SELECT count(*) FROM phpbb_posts WHERE forum_id = 19 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: phpbb_posts
         type: ref
possible_keys: fid_tid_post_time,forum_id
          key: fid_tid_post_time
      key_len: 3
          ref: const
         rows: 161103
        Extra: Using index
1 row in set (0.00 sec)

Strange why forum_id was not used here. Please try to run OPTIMIZE TABLE phpbb_posts and check if plan is changes.

Please also try queries with your table in version 5.6 to be sure if this is duplicate of bug #32254 and not new problem.

On 11 May 17:06 Sveta Smirnova wrote:
> Please try to run OPTIMIZE TABLE phpbb_posts and check if plan is changes.

It didn't change:

mysql> use forum;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> EXPLAIN SELECT count(*) FROM phpbb_posts WHERE forum_id = 19 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: phpbb_posts
         type: ref
possible_keys: fid_tid_post_time,forum_id
          key: fid_tid_post_time
      key_len: 3
          ref: const
         rows: 161103
        Extra: Using index
1 row in set (0.14 sec)

mysql> OPTIMIZE TABLE phpbb_posts;
+-------------------+----------+----------+----------+
| Table             | Op       | Msg_type | Msg_text |
+-------------------+----------+----------+----------+
| forum.phpbb_posts | optimize | status   | OK       |
+-------------------+----------+----------+----------+
1 row in set (7.15 sec)

mysql> EXPLAIN SELECT count(*) FROM phpbb_posts WHERE forum_id = 19 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: phpbb_posts
         type: ref
possible_keys: fid_tid_post_time,forum_id
          key: fid_tid_post_time
      key_len: 3
          ref: const
         rows: 161103
        Extra: Using index
1 row in set (0.00 sec)

> Please also try queries with your table in version 5.6

I'll try that next.

On 11 May 17:06 Sveta Smirnova wrote:
> Please also try queries with your table in version 5.6

That made no difference either:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.5-m8-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use forum;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> EXPLAIN SELECT count(*) FROM phpbb_posts WHERE forum_id = 19 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: phpbb_posts
         type: ref
possible_keys: fid_tid_post_time,forum_id
          key: fid_tid_post_time
      key_len: 3
          ref: const
         rows: 161103
        Extra: Using index
1 row in set (0.00 sec)

mysql> OPTIMIZE TABLE phpbb_posts;
+-------------------+----------+----------+-----------------------------+
| Table             | Op       | Msg_type | Msg_text                    |
+-------------------+----------+----------+-----------------------------+
| forum.phpbb_posts | optimize | status   | Table is already up to date |
+-------------------+----------+----------+-----------------------------+
1 row in set (0.00 sec)

mysql> ANALYZE TABLE phpbb_posts;
+-------------------+---------+----------+-----------------------------+
| Table             | Op      | Msg_type | Msg_text                    |
+-------------------+---------+----------+-----------------------------+
| forum.phpbb_posts | analyze | status   | Table is already up to date |
+-------------------+---------+----------+-----------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT count(*) FROM phpbb_posts WHERE forum_id = 19 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: phpbb_posts
         type: ref
possible_keys: fid_tid_post_time,forum_id
          key: fid_tid_post_time
      key_len: 3
          ref: const
         rows: 161103
        Extra: Using index
1 row in set (0.00 sec)

mysql>

Er, I forgot the original query in my last comment. Here it is still using index_merge on 5.6.5:

mysql> EXPLAIN SELECT post_id, topic_id, forum_id FROM phpbb_posts WHERE topic_id = 110126 AND forum_id = 19 ORDER BY post_time ASC LIMIT 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: phpbb_posts
         type: index_merge
possible_keys: topic_id,tid_post_time,tid_fid,fid_tid_post_time,forum_id
          key: topic_id,forum_id
      key_len: 3,3
          ref: NULL
         rows: 1592
        Extra: Using intersect(topic_id,forum_id); Using where; Using filesort
1 row in set (0.00 sec)

Thank you for the feedback.

Verified as described. Simplified test case will be uploaded to internal server.

Queries for simplified test case:

mysql> SELECT post_id, topic_id, forum_id FROM t1 WHERE topic_id = 35 AND forum_id = 19 ORDER BY post_time ASC LIMIT 1;
+---------+----------+----------+
| post_id | topic_id | forum_id |
+---------+----------+----------+
|   11394 |       35 |       19 |
+---------+----------+----------+
1 row in set (0.56 sec)

mysql> SELECT post_id, topic_id, forum_id FROM t1 ignore index(forum_id) WHERE topic_id = 35 AND forum_id = 19 ORDER BY post_time ASC LIMIT 1;
+---------+----------+----------+
| post_id | topic_id | forum_id |
+---------+----------+----------+
|   11394 |       35 |       19 |
+---------+----------+----------+
1 row in set (0.28 sec)

mysql> explain SELECT post_id, topic_id, forum_id FROM t1 WHERE topic_id = 35 AND forum_id = 19 ORDER BY post_time ASC LIMIT 1;
+----+-------------+-------+-------------+---------------------------+-------------------+---------+------+------+-----------------------------------------------------------------+
| id | select_type | table | type        | possible_keys             | key               | key_len | ref  | rows | Extra                                                           |
+----+-------------+-------+-------------+---------------------------+-------------------+---------+------+------+-----------------------------------------------------------------+
|  1 | SIMPLE      | t1    | index_merge | topic_id,tid_fid,forum_id | forum_id,topic_id | 3,3     | NULL | 2200 | Using intersect(forum_id,topic_id); Using where; Using filesort |
+----+-------------+-------+-------------+---------------------------+-------------------+---------+------+------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain SELECT post_id, topic_id, forum_id FROM t1 ignore index(forum_id) WHERE topic_id = 35 AND forum_id = 19 ORDER BY post_time ASC LIMIT 1;
+----+-------------+-------+------+------------------+---------+---------+-------------+-------+-----------------------------+
| id | select_type | table | type | possible_keys    | key     | key_len | ref         | rows  | Extra                       |
+----+-------------+-------+------+------------------+---------+---------+-------------+-------+-----------------------------+
|  1 | SIMPLE      | t1    | ref  | topic_id,tid_fid | tid_fid | 6       | const,const | 84294 | Using where; Using filesort |
+----+-------------+-------+------+------------------+---------+---------+-------------+-------+-----------------------------+
1 row in set (0.00 sec)

And here is the explanation: index merge allows to examine only 2200 rows instead of 84294 with composite index (x38 times), although it badly affects performance.

Workarounds:

1. Use FORCE INDEX
2. SET optimizer_switch='index_merge=off'

Bug #65359 was marked as duplicate of this one.

Noted in 5.6.7, 5.7.0 changelogs.

For some queries, the optimizer used index_merge access method when
this was more work than ref access.