|
For the following query
let $query =
|
SELECT SUM(b) FROM ( SELECT * FROM t1 ) AS alias1, t2
|
WHERE b <= ANY (
|
SELECT a FROM t1
|
WHERE a = b + SLEEP(0.2) OR a >= ( SELECT SUM(b) FROM t2 )
|
)
|
EXPLAIN returns
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY t1 index NULL a 4 NULL 2 Using index
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
|
3 DEPENDENT SUBQUERY t1 index a a 4 NULL 2 Using where; Using index
|
4 SUBQUERY t2 ALL NULL NULL NULL NULL 2
|
and SHOW EXPLAIN produces
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY t1 index NULL a 4 NULL 2 Using index
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
|
3 DEPENDENT SUBQUERY t1 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
|
4 SUBQUERY t2 ALL NULL NULL NULL NULL 2
|
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)
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:
|
CREATE TABLE t1 (a INT NOT NULL, KEY(a)) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES (7),(0);
|
|
CREATE TABLE t2 (b INT NOT NULL) ENGINE=MyISAM;
|
INSERT INTO t2 VALUES (0),(8);
|
|
let $query =
|
SELECT SUM(b) FROM ( SELECT * FROM t1 ) AS alias1, t2
|
WHERE b <= ANY (
|
SELECT a FROM t1
|
WHERE a = b + SLEEP(0.2) OR a >= ( SELECT SUM(b) FROM t2 )
|
);
|
|
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, t2;
|
|