incorrect/inefficient choice of index

[Kristian Kauper]

Hi All,

I realise that many people have asked about InnoDBs often seemingly bizarre choice of which index to use to satisfy a query, but in this case, I suspect that it might be a bug:

I have a replication cluster with four hosts -- a master and 2 slaves running MySQL v4.1 and one slave running v5.1.24. For one particular query, the v4.1 slaves appear to make a reasonable choice of index, but the v5.1 slave does not (that's why I think it could possibly be a bug, which has been introduced since v4.1)

Here's my query:

select thread.*
from items as seed,
items as thread
where thread.thread_id = seed.thread_id
and thread.status_code < 4
and seed.id = 4336668
order by thread.id desc;

Among others, I have these indexes:

PRIMARY (id)
thread_id_idx (thread_id, id)

Here's the query plan MySQL chooses:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: seed
type: const
possible_keys: PRIMARY,thread_status_idx,thread_id_idx,thread_idx
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: thread
type: index
possible_keys: items_status_code,search_idx,status_collection_idx,thread_status_idx,thread_id_idx,search2,thread_idx
key: PRIMARY
key_len: 4
ref: NULL
rows: 482894
Extra: Using where

What I can't figure out is why InnoDB is using PRIMARY as the key for the "thread" table. It seems to be doing so because of the "order by" clause, but that forces it to make 482894 comparisons to satisfy the join condition.

It makes *much* better sense to use the "thread_id_idx" index, which allow it to satisfy both the join condition and order clauses. In fact, if I force the use of that index, the results are much better:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: seed
type: const
possible_keys: PRIMARY,thread_status_idx,thread_id_idx,thread_idx
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: thread
type: ref
possible_keys: thread_id_idx
key: thread_id_idx
key_len: 5
ref: const
rows: 1
Extra: Using where

The latter query takes less than 10 ms to execute, where the former takes 1-2 seconds.

Can anyone throw any light on this?

BTW, I've run both analyze and optimize on the table. For reference, here is the full table definition:

| items | CREATE TABLE `items` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`published` timestamp NULL DEFAULT NULL,
`activates` timestamp NULL DEFAULT NULL,
`expires` timestamp NULL DEFAULT NULL,
`type_code` int(3) unsigned NOT NULL DEFAULT '7',
`keywords` varchar(255) DEFAULT NULL,
`category_id` int(11) unsigned NOT NULL DEFAULT '1',
`locator_codes` int(255) unsigned DEFAULT NULL,
`priority` int(11) unsigned NOT NULL DEFAULT '5',
`source` varchar(255) DEFAULT NULL,
`copyright` varchar(255) DEFAULT NULL,
`programs` varchar(255) DEFAULT NULL,
`item_group_id` int(3) unsigned DEFAULT NULL,
`collection_id` int(10) unsigned DEFAULT NULL,
`title` varchar(255) NOT NULL DEFAULT '',
`status_code` int(3) unsigned NOT NULL DEFAULT '1',
`thread_id` int(11) unsigned DEFAULT '0',
`modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `items_type_code` (`type_code`),
KEY `items_category_code` (`category_id`),
KEY `items_status_code` (`status_code`),
KEY `search_idx` (`status_code`,`expires`,`activates`,`type_code`,`priority`,`collection_id`),
KEY `status_collection_idx` (`status_code`,`collection_id`),
KEY `thread_status_idx` (`thread_id`,`status_code`),
KEY `thread_id_idx` (`thread_id`,`id`),
KEY `published` (`published`),
KEY `search2` (`status_code`,`type_code`,`published`,`id`),
KEY `thread_idx` (`thread_id`),
CONSTRAINT `items_ibfk_1` FOREIGN KEY (`type_code`) REFERENCES `type_codes` (`code`),
CONSTRAINT `items_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `category_codes` (`id`),
CONSTRAINT `items_ibfk_3` FOREIGN KEY (`status_code`) REFERENCES `status_codes` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=5018959 DEFAULT CHARSET=utf8

Here are the current index stats (they're very similar for both the v4.1 slaves and the v5.1 slave):

mysql> show indexes from items;
+-------+------------+-----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| items | 0 | PRIMARY | 1 | id | A | 461945 | NULL | NULL | | BTREE | |
| items | 1 | items_type_code | 1 | type_code | A | 114 | NULL | NULL | | BTREE | |
| items | 1 | items_category_code | 1 | category_id | A | 12 | NULL | NULL | | BTREE | |
| items | 1 | items_status_code | 1 | status_code | A | 12 | NULL | NULL | | BTREE | |
| items | 1 | search_idx | 1 | status_code | A | 19 | NULL | NULL | | BTREE | |
| items | 1 | search_idx | 2 | expires | A | 4088 | NULL | NULL | YES | BTREE | |
| items | 1 | search_idx | 3 | activates | A | 18477 | NULL | NULL | YES | BTREE | |
| items | 1 | search_idx | 4 | type_code | A | 20084 | NULL | NULL | | BTREE | |
| items | 1 | search_idx | 5 | priority | A | 20084 | NULL | NULL | | BTREE | |
| items | 1 | search_idx | 6 | collection_id | A | 20084 | NULL | NULL | YES | BTREE | |
| items | 1 | status_collection_idx | 1 | status_code | A | 12 | NULL | NULL | | BTREE | |
| items | 1 | status_collection_idx | 2 | collection_id | A | 12 | NULL | NULL | YES | BTREE | |
| items | 1 | thread_status_idx | 1 | thread_id | A | 461945 | NULL | NULL | YES | BTREE | |
| items | 1 | thread_status_idx | 2 | status_code | A | 461945 | NULL | NULL | | BTREE | |
| items | 1 | thread_id_idx | 1 | thread_id | A | 461945 | NULL | NULL | YES | BTREE | |
| items | 1 | thread_id_idx | 2 | id | A | 461945 | NULL | NULL | | BTREE | |
| items | 1 | published | 1 | published | A | 461945 | NULL | NULL | YES | BTREE | |
| items | 1 | search2 | 1 | status_code | A | 19 | NULL | NULL | | BTREE | |
| items | 1 | search2 | 2 | type_code | A | 19 | NULL | NULL | | BTREE | |
| items | 1 | search2 | 3 | published | A | 461945 | NULL | NULL | YES | BTREE | |
| items | 1 | search2 | 4 | id | A | 461945 | NULL | NULL | | BTREE | |
| items | 1 | thread_idx | 1 | thread_id | A | 461945 | NULL | NULL | YES | BTREE | |
+-------+------------+-----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+