On 12/11/2010 09:21 AM, Øystein Grøvlen wrote:
> Thanks Tor, Looks good. Approved.
> See below for one question:
>> === modified file 'mysql-test/r/myisam_mrr_cost_icp.result'
>> --- a/mysql-test/r/myisam_mrr_cost_icp.result 2010-12-06 13:10:10 +0000
>> +++ b/mysql-test/r/myisam_mrr_cost_icp.result 2010-12-10 15:00:25 +0000
>> @@ -347,7 +347,7 @@ GROUP BY t2.pk
>> id select_type table type possible_keys key key_len ref rows filtered
>> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
>> -2 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using index
>> condition; Using where; Using filesort
>> +2 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using index condition
>> Note 1003 select min(`test`.`t1`.`pk`) AS `MIN(t1.pk)` from
>> `test`.`t1` where 0
>> DROP TABLE t1, t2;
> For this query, if I replace GROUP BY with ORDER BY, "Using filesort" is
> still there. Is that a reason for concern?
It makes no sense to maintain ordering in an EXISTS subquery because we're only
interested in whether or not there is (at least) one record in the subquery
result. Hence, the rewritten GROUP BY is actually more correct than the plan for
Tor, a possible extension to your patch is to unconditionally remove order by
from exists subqueries like we do for IN/ALL/ANY, but I understand if you don't
want to do this as part of this bugfix.
Jørgen Løland | Senior Software Engineer | +47 73842138