MySQL Bugs: #44969: index not being used anymore with order by query

Bug #44969	index not being used anymore with order by query
Submitted:	19 May 2009 23:31	Modified:	11 Dec 2009 10:43
Reporter:	Mr Jay	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	5.1.34, 5.1.35, 5.1.38, 6.0 bzr	OS:	Linux (x64)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	Indexing, optimization, order by, regression

Description:
I recently upgraded from 5.0.51 to 5.1.34
I have a query and a 4gb table:
select id from a where b=7161 and c=0 and id >=1 and id <=9405 order by id

The server used the proper index with 5.0.51, but with 5.1.34 it doesn't anymore. If I remove "order by id" it works with correct index again.

Table:
CREATE TABLE `a` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `b` bigint(20) unsigned NOT NULL,
  `c` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`,`b`,`c`),
  KEY `index_1` (`b`),
  KEY `index_1` (`b`,`c`)

Explain with 5.1.34:
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  a   range   PRIMARY,index_1,index_2       PRIMARY 12      NULL    3032839 Using where

Explain with 5.0.51:
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  a   ref     PRIMARY,index_1,index_2       index_2    8       const,const     20      Using where; Using filesort

The query now takes longer time (~10000 times longer) because of wrong index being used. Forcing to use an index isn't the best solution..

How to repeat:
CREATE TABLE `a` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `b` bigint(20) unsigned NOT NULL,
  `c` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`,`b`,`c`),
  KEY `index_1` (`b`),
  KEY `index_1` (`b`,`c`)

Thank you for the problem report. Please, send correct CREATE TABLE statement. I do not see index2 defined for your table at all.

What is more important, please, send the results of:

select count(*) from a where b=7161 and c=0;

Sorry,

CREATE TABLE `a` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `b` bigint(20) unsigned NOT NULL,
  `c` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`,`b`,`c`),
  KEY `index_1` (`b`),
  KEY `index_2` (`b`,`c`)

select count(*) from a where b=7161 and c=0
>> 59

select count(*) from a
>> 9341691

select count(*) from a where b=7161
>> 3348

Correction,

CREATE TABLE `a` (
  `id` int(10) unsigned NOT NULL,
  `b` int(10) unsigned NOT NULL,
  `c` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`,`b`,`c`),
  KEY `index_1` (`b`),
  KEY `index_2` (`b`,`c`)

(bigint changed to int)

Thank you for the feedback.

Verified as described. Version 5.0 is not affected.

test case for the testsuite

Attachment: bug44969.test (application/octet-stream, text), 583 bytes.

I am working with a client who seems to be affected by this bug.  We found that changing the order the indexes are defined is important.  Moving the index_2 index right after the primary key in the CREATE TABLE definition, above index_1, would cause it to be correctly used.  (I have not verified that behavior with the test case given in this bug report.)

Even with 19000 rows it MySQL 5.0 15 times faster:

mysql51> select id from a where b=7161 and c=0 and id >=1 and id <=940534323 order by id;
...
| 870864744 | 
+-----------+
25 rows in set (0.66 sec)

mysql50> select id from a where b=7161 and c=0 and id >=1 and id <=940534323 order by id;
...
| 870864744 | 
+-----------+
25 rows in set (0.03 sec)

I would suggest that the severity be moved to critical - I, too, have a 4GB table with 50+ users and this bug is bringing my system to its knees.

Bug #45559 was marked as a duplicate of this one.

Hi,

Actually order by queries don't seems to be the only ones affected by this issue ( see my last report in http://bugs.mysql.com/bug.php?id=36817 ), which is indeed quite annoying...

Regards,
  Jocelyn Fournier

Bug #49391 was marked as a duplicate of this one.

This bug is a duplicate of bug #45828 (already closed).