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

EXPLAIN shows type=range, while SHOW EXPLAIN and userstat show full table scan is used

Description

For the following query

1 2 SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 WHERE SLEEP(0.2) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 )

usual EXPLAIN produces

1 2 3 4 5 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 range b b 5 NULL 2 Using index condition; Using where 1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away

while SHOW EXPLAIN says

1 2 3 4 5 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where 1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away

As always, SLEEP in the query is not essential, it just makes the query a bit longer and allows to catch it by SHOW EXPLAIN.

bzr version-info (5.5-show-explain-test1)

1 2 3 4 revision-id: psergey@askmonty.org-20120719215203-m2p9cbqb37n0th7n date: 2012-07-20 01:52:03 +0400 build-date: 2012-07-24 04:43:23 +0400 revno: 3456

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 57 58 59 CREATE TABLE t1 (a VARCHAR(3) PRIMARY KEY) ENGINE=MyISAM; INSERT INTO t1 VALUES ('USA'); CREATE TABLE t2 (b INT, c VARCHAR(52), KEY(b)) ENGINE=MyISAM; INSERT INTO t2 VALUES (3813,'United States'),(3940,'Russia'); CREATE TABLE t3 (d INT, KEY(d)) ENGINE=MyISAM; INSERT INTO t3 VALUES (12),(22),(9),(45); EXPLAIN SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 WHERE SLEEP(0.2) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); --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 SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 WHERE SLEEP(0.2) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); --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, t2, t3;

Environment

None

Status

Assignee

Sergei Petrunia

Reporter

Elena Stepanova

Labels

None

External issue ID

None

External issue ID

None

Fix versions

Priority

Major