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

[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).

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.073 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.