We're updating the issue view to help you get more done. 

Extra field 'Using filesort' in EXPLAIN vs 'Using where; Using filesort' in SHOW EXPLAIN on a query with HAVING and ORDER BY

Description

1 2 3 4 5 6 7 8 9 10 11 12 13 EXPLAIN SELECT alias1.b FROM t1 AS alias1 LEFT JOIN t1 AS alias2 ON (alias2.b = alias1.a) WHERE EXISTS ( SELECT * FROM t1 WHERE a <= alias2.a ) HAVING alias1.b > 10 ORDER BY alias1.b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY alias1 ALL NULL NULL NULL NULL 9 Using filesort 1 PRIMARY alias2 ref b b 5 test.alias1.a 2 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using where #--------------- # SHOW EXPLAIN output: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY alias1 ALL NULL NULL NULL NULL 9 Using where; Using filesort 1 PRIMARY alias2 ref b b 5 test.alias1.a 2 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using where

bzr version-info

1 2 3 4 revision-id: psergey@askmonty.org-20120801115152-qgxtqd0a5vlqrrhx date: 2012-08-01 15:51:52 +0400 build-date: 2012-08-02 16:12:26 +0300 revno: 3461

Default optimizer_switch:

1 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off

Test case:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 CREATE TABLE t1 (a INT, b INT, KEY(b)) ENGINE=MyISAM; INSERT INTO t1 VALUES (0,4),(8,6),(1,3),(8,5),(9,3),(2,4),(6,2),(1,9),(6,3); let $query = SELECT alias1.b FROM t1 AS alias1 LEFT JOIN t1 AS alias2 ON (alias2.b = alias1.a) WHERE EXISTS ( SELECT * FROM t1 WHERE a <= alias2.a ) HAVING alias1.b > 10 ORDER BY alias1.b; eval EXPLAIN $query; --echo #--------------- --echo # SHOW EXPLAIN output: --connect (con1,localhost,root,,) --let $con_id = `SELECT CONNECTION_ID()` --let $trials = 50 --disable_query_log while ($trials) { --dec $trials --let $run = 1000 --send_eval $query --connection default while ($run) { --error 0,1932 eval SHOW EXPLAIN FOR $con_id; --dec $run if (!$mysql_errno) { --let $run = 0 --let $trials = 0 --let $found = 1 } } --disable_result_log --connection con1 --reap --enable_result_log } if (!$found) { --echo ########### Could not catch the query by SHOW EXPLAIN, try again ############# } DROP TABLE t1;

Environment

None

Status

Assignee

Sergei Petrunia

Reporter

Elena Stepanova

Labels

None

External issue ID

None

External issue ID

None

Fix versions

Priority

Minor