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:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:4.1.14 OS:Any (All)
Assigned to: CPU Architecture:Any
Triage: D5 (Feature request)

[25 Sep 2005 10:10] Alexander Drozdov
Description:
"How do repeat" section describes my problem.

How to repeat:
1. Create tables and content:

create table t1(
  a int(1) not null,
  b int(1) not null,
  index a(a),
  index ab(a,b),
  index ba(b,a));
create table t2(
  a int(1) not null,
  c int(1) not null,
  index ac(a,c));
insert into t1 values(1,1);
insert into t1 values(2,1);
insert into t1 values(3,1);
insert into t1 values(4,2);
insert into t1 values(5,2);
insert into t1 values(6,3);
insert into t1 values(7,3);
insert into t1 values(8,3);

insert into t2 values(1,1);
insert into t2 values(1,2);
insert into t2 values(1,3);
insert into t2 values(2,1);
insert into t2 values(2,4);
insert into t2 values(2,6);
insert into t2 values(3,2);
insert into t2 values(3,3);
insert into t2 values(3,4);

2. Run some queries:
mysql> explain select t2.a,b,c from t1, t2 where b=1 and t1.a=t2.a order by t1.a;
+----+-------------+-------+------+---------------+------+---------+------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref        | rows | Extra                    |
+----+-------------+-------+------+---------------+------+---------+------------+------+--------------------------+
|  1 | SIMPLE      | t1    | ref  | a,ab,ba       | ba   |       4 | const      |    3 | Using where; Using index |
|  1 | SIMPLE      | t2    | ref  | ac            | ac   |       4 | alpex.t1.a |    1 | Using index              |
+----+-------------+-------+------+---------------+------+---------+------------+------+--------------------------+
2 rows in set (0.00 sec)

It's OK!

(you can run "explain select t2.a,b,c from t1, t2 where b=1 and t1.a=t2.a order by t2.a" and see that Extra column changed, but it is not the problem I reporting)

mysql> explain select t2.a,b,c from t1, t2 where b=1 and t1.a=t2.a order by t1.a, c;
+----+-------------+-------+------+---------------+------+---------+------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref        | rows | Extra                                                     |
+----+-------------+-------+------+---------------+------+---------+------------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | t1    | ref  | a,ab,ba       | ba   |       4 | const      |    3 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | t2    | ref  | ac            | ac   |       4 | alpex.t1.a |    1 | Using index                                               |
+----+-------------+-------+------+---------------+------+---------+------------+------+-----------------------------------------------------------+
2 rows in set (0.00 sec)

"Using temporary; Using filesort"? But 'ba' index returns right order of the 'a' column.

Let use LEFT JOIN syntax:
mysql> explain select t2.a,b,c from t1 left join t2 using(a) where b=1 and c is not null order by t1.a, c;
+----+-------------+-------+------+---------------+------+---------+------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref        | rows | Extra                                                     |
+----+-------------+-------+------+---------------+------+---------+------------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | t1    | ref  | ba            | ba   |       4 | const      |    3 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | t2    | ref  | ac            | ac   |       4 | alpex.t1.a |    1 | Using where; Using index                                  |
+----+-------------+-------+------+---------------+------+---------+------------+------+-----------------------------------------------------------+
2 rows in set (0.00 sec)

The same result. The same result will be if we change order:
mysql> explain select t2.a,b,c from t1 left join t2 using(a) where b=1 and c is not null order by t2.a, c;
mysql> explain select t2.a,b,c from t1, t2 where b=1 and t1.a=t2.a order by t2.a, c;
[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.