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: Gleb Shchepa CPU Architecture:Any
Tags: Indexing, optimization, order by, regression
Triage: Needs Triage: D3 (Medium) / R6 (Needs Assessment) / E6 (Needs Assessment)

[19 May 2009 23:31] Mr Jay
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`)
[20 May 2009 8:00] Valeriy Kravchuk
Thank you for the problem report. Please, send correct CREATE TABLE statement. I do not see index2 defined for your table at all.
[20 May 2009 8:07] Valeriy Kravchuk
What is more important, please, send the results of:

select count(*) from a where b=7161 and c=0;
[21 May 2009 11:03] Mr Jay
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
[21 May 2009 19:31] Mr Jay
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)
[26 May 2009 20:30] Sveta Smirnova
Thank you for the feedback.

Verified as described. Version 5.0 is not affected.
[26 May 2009 20:30] Sveta Smirnova
test case for the testsuite

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

[30 May 2009 3:20] Baron Schwartz
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.)
[2 Jun 2009 6:26] Sveta Smirnova
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)
[18 Jun 2009 20:24] Jon Baker
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.
[19 Jun 2009 6:04] Valeriy Kravchuk
Bug #45559 was marked as a duplicate of this one.
[30 Jul 2009 0:13] jocelyn fournier
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
[4 Dec 2009 8:53] Valeriy Kravchuk
Bug #49391 was marked as a duplicate of this one.
[11 Dec 2009 10:43] Gleb Shchepa
This bug is a duplicate of bug #45828 (already closed).