Bug #27479 ORDER BY performance regression
Submitted: 27 Mar 2007 18:12 Modified: 30 May 2007 7:26
Reporter: Steven Wagner Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.0.37 OS:Linux (Linux 2.6)
Assigned to: CPU Architecture:Any
Tags: regression

[27 Mar 2007 18:12] Steven Wagner 
Description:
SELECT DISTINCT a.id, a.id2 
FROM mytable a, mytable b
WHERE a.id2 = b.id2 AND a.id != b.id 
ORDER BY a.id2;

The explain shows no indexes are used. This query completes in about 10 seconds on mysql 5.0.22 , 5.0.19.  But on MySQL 5.0.27 and 5.0.37, it takes 3 or 4 minutes to complete.

The query time drops down to 10 seconds on all versions if the ORDER BY is removed from the query.

How to repeat:
It should be repeatable. I am testing on table with about 8000 rows of data in it.
[28 Mar 2007 4:55] Valeriy Kravchuk 
Thank you for a problem report. Please, send dumnp of your tables to simplify verification.
[28 Apr 2007 23:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[30 Apr 2007 7:26] Valeriy Kravchuk 
Please, send the results of 

SHOW CREATE TABLE mytable;

How many rows does your SELECT return?
[30 May 2007 23:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".