Bug #13468 | Mysql optimizer decides to use tempfile and filesort when it's not necessary | ||
---|---|---|---|
Submitted: | 25 Sep 2005 10:10 | Modified: | 17 Aug 2007 22:21 |
Reporter: | Alexander Drozdov | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S4 (Feature request) |
Version: | 4.1.14 | OS: | Any (All) |
Assigned to: | CPU Architecture: | Any |
[25 Sep 2005 10:10]
Alexander Drozdov
[25 Sep 2005 10:42]
Valeriy Kravchuk
Verified on 4.1.14 - the results are the same as in the bug report. But, according to the manual (http://dev.mysql.com/doc/mysql/en/order-by-optimization.html): "In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following: ... - You are joining many tables, and the columns in the ORDER BY are not all from the first non-constant table that is used to retrieve rows. (This is the first table in the EXPLAIN output that does not have a const join type.)" This is our case. I do not know, really, why not to use t2 as outer (first) table, select rows ordered from it using index, and then just pick a row from t1. But this is how it works now. Changed severity to a feture request, as a result.
[25 Sep 2005 13:16]
Alexander Drozdov
> I do not know, really, why not to use t2 as outer > (first) table, select rows ordered from it using index, and then just > pick a row from t1. In my case t2 table is bigger than t1. If I use t2 as outer table than all t2 table will be readed. In your case, "explain select t1.a, b, c from t2 left join t1 using(a) where b='1' order by t2.a, c;" doesn't work (filesorting because t1 is the first table anyway), but "explain select t1.a, b, c from t2 straight_join t1 where b='1' and t1.a=t2.a order by t2.a, c;" works.
[17 Aug 2007 22:21]
Igor Babaev
- This is a feature request: the customer wants a new optimization that will require a non-trivial analysis of functional dependency between tables. - The implementation of this functionality will require 1 man-month. By the above reasons I move the bug to 'To be fixed later'. Product management will decide in what version a fix for this problem appears.