Bug #4981 4.x and 5.x produce non-optimal execution path, 3.23 regression test failure
Submitted: 10 Aug 2004 19:28 Modified: 3 Jul 2006 18:59
Reporter: Matthew Lord Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:4.x/5.x OS:Any (all)
Assigned to: Georgi Kodinov CPU Architecture:Any

[10 Aug 2004 19:28] Matthew Lord
Description:
I got the same explain output using x86 linux and windows 2000.

3.23 execution path fulfills the query in ~ 2-5 minutes while the 4.x/5.x execution path
takes days.

How to repeat:
Run the internally posted query to see the explain output as well as the execution time.  See CSC 
#3206 for any additional information.

Suggested fix:
Not sure on this one but it's at least a regression test issue.
[20 Aug 2004 13:09] Ramil Kalimullin
Looks like the problem is with the 'limit 0,20' clause.
Matthew, could you try the query without 'limit', please?
 (I got a proper result)
[20 Aug 2004 13:13] Ramil Kalimullin
Or rather, the problem is the 'order by' and 'limit' combination.
[20 Aug 2004 20:12] Matthew Lord
Hi Ramil,

Yes, this appears to be due to the combination of the order by and the limit.

Best Regards
[1 Jun 2006 8:52] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7144
[2 Jun 2006 9:01] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7204
[2 Jun 2006 15:18] Georgi Kodinov
There are two problems that were revealed by that bug report : 
1. The member SEL_ARG::min_flag was not initialized,  due to which the condition for GEOM_FLAG in function key_or did not choose "Range checked for each record" as the correct access method.
2. Non-optimal join order is selected because of the fact that the cost calculation is not taking into account the savings introduced by "Range checked for each record" access method.

The applied patch fixes 1. but since 2. is a hard problem that requires a major change that may affect many users it will probably not be fixed in the current 4.x/5.x branches. 
So for the time being I suggest using the STRAIGHT_JOIN option of the SELECT statement : put the tables in the FROM clause in the correct join order and add STRAIGHT_JOIN option.
[19 Jun 2006 0:12] Evgeny Potemkin
Fixed in 4.1.21, 5.0.23, 5.1.12
[3 Jul 2006 18:59] Paul DuBois
Noted in 4.1.21, 5.0.23, 5.1.12 changelogs.

Some queries that used ORDER BY and LIMIT performed quickly in MySQL
3.23, but slowly in MySQL 4.x/5.x due to an optimizer problem.
[2 Oct 2006 20:27] B K
I don't see any better performance in any of the listed version or later (incl. 5.0.24a).  Please see bug 12113.
[16 Oct 2006 14:01] Georgi Kodinov
B K,
see my previous comment : my fix fixed the error that prevents selecting range access even if the correct join order is forced by using the STRAIGHT_JOIN option. It doesn't fix the way the join order is selected. It is mentioned as point 2 in my comment.