MySQL Bugs: #54799: optimizer does not pick the index

Bug #54799	optimizer does not pick the index
Submitted:	25 Jun 2010 8:50	Modified:	25 Jun 2010 10:51
Reporter:	Aleksandar Ivanisevic	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.5.3-m3	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:

| db_ct_schedule_item | CREATE TABLE `db_ct_schedule_item` (
  `schedule_item_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `item_id_number` varchar(10) NOT NULL,
  `start_date` char(8) DEFAULT NULL,
  `item_type` enum('PAIRING','ACTIVITY') NOT NULL,
  `start_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `report_base` char(3) NOT NULL,
  `end_base` char(3) NOT NULL,
  PRIMARY KEY (`schedule_item_id`),
  KEY `idx_db_ct_schedule_item_1` (`start_time`,`item_id_number`)
) ENGINE=InnoDB AUTO_INCREMENT=665350 DEFAULT CHARSET=utf8 |

5.5.1-m2 correctly picks the index idx_db_ct_schedule_item_1 for the following query

mysql>  explain select count(*) from db_ct_schedule_item where item_id_number = 'ERR' and start_date = '20100626' and item_type = 'ACTIVITY' and start_time = '2010-06-26 04:01:00.0' and end_time = '2010-06-27 01:43:00.0' and report_base = 'JFK' and end_base = 'JFK' ;
+----+-------------+---------------------+------+---------------------------+---------------------------+---------+-------------+------+-------------+
| id | select_type | table               | type | possible_keys             | key                       | key_len | ref         | rows | Extra       |
+----+-------------+---------------------+------+---------------------------+---------------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | db_ct_schedule_item | ref  | idx_db_ct_schedule_item_1 | idx_db_ct_schedule_item_1 | 41      | const,const | 1324 | Using where |
+----+-------------+---------------------+------+---------------------------+---------------------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

but 5.5.3-m3 doesn't want it, not even when forced

jbui>  explain select count(*) from db_ct_schedule_item where item_id_number = 'ERR' and start_date = '20100626' and item_type = 'ACTIVITY' and start_time = '2010-06-26 04:01:00.0' and end_time = '2010-06-27 01:43:00.0' and report_base = 'JFK' and end_base = 'JFK' ;
+----+-------------+---------------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table               | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | db_ct_schedule_item | ALL  | NULL          | NULL | NULL    | NULL | 659102 | Using where |
+----+-------------+---------------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

jbui>  explain select count(*) from db_ct_schedule_item force index(idx_db_ct_schedule_item_1) where item_id_number = 'ERR' and start_date = '20100626' and item_type = 'ACTIVITY' and start_time = '2010-06-26 04:01:00.0' and end_time = '2010-06-27 01:43:00.0' and report_base = 'JFK' and end_base = 'JFK' ;
+----+-------------+---------------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table               | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | db_ct_schedule_item | ALL  | NULL          | NULL | NULL    | NULL | 659135 | Using where |
+----+-------------+---------------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.01 sec)

How to repeat:

I can provide the dump of db_ct_schedule_item if required

Please, upload the dump.

uploaded to the ftp server

This is what I've got on current mysql-trunk:

openxs@ubuntu:/home2/openxs/dbs/trunk$ bin/mysql --no-defaults -uroot testWelcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.6-m3-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> exit
Bye
openxs@ubuntu:/home2/openxs/dbs/trunk$ bin/mysql --no-defaults -uroot test < ~/bug54799.sql 
openxs@ubuntu:/home2/openxs/dbs/trunk$ bin/mysql --no-defaults -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.6-m3-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> explain select count(*) from db_ct_schedule_item where item_id_number = 'ERR' and
    -> start_date = '20100626' and item_type = 'ACTIVITY' and start_time = '2010-06-26
    '> 04:01:00.0' and end_time = '2010-06-27 01:43:00.0' and report_base = 'JFK' and end_base =
    -> 'JFK' ;
+----+-------------+---------------------+------+---------------------------+---------------------------+---------+-------------+------+-------------+
| id | select_type | table               | type | possible_keys             | key                       | key_len | ref         | rows | Extra       |
+----+-------------+---------------------+------+---------------------------+---------------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | db_ct_schedule_item | ref  | idx_db_ct_schedule_item_1 | idx_db_ct_schedule_item_1 | 41      | const,const | 1324 | Using where |
+----+-------------+---------------------+------+---------------------------+---------------------------+---------+-------------+------+-------------+
1 row in set (0.40 sec)

So, I'd suggest to wait for the official release of 5.5.5 and then check again.

can you tell me when is 5.5.5 scheduled to be released? I have already upgraded my test databases and this is affecting their performance severely, so I'll have to decide whether to downgrade or not.