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