|
See 'index_subqu' in the output below:
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
|
2 DEPENDENT SUBQUERY t2 index_subqu PRIMARY,c c 5 func 1 Using index; Using where
|
Standard EXPLAIN looks normal:
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
|
2 DEPENDENT SUBQUERY t2 index_subquery PRIMARY,c c 5 func 1 Using index; Using where
|
SELECT SUM(a + SLEEP(0.1)) FROM t1 WHERE a IN ( SELECT c FROM t2 WHERE d < b ) OR b < 's';
|
bzr version-info
revision-id: psergey@askmonty.org-20120719215203-m2p9cbqb37n0th7n
|
date: 2012-07-20 01:52:03 +0400
|
build-date: 2012-07-23 03:03:22 +0400
|
revno: 3456
|
Test case (SLEEP is here only to make the query longer, otherwise not needed):
--source include/have_innodb.inc
|
|
CREATE TABLE t1 (a INT, b VARCHAR(35)) ENGINE=InnoDB;
|
INSERT INTO t1 VALUES (3989,'Abilene'),(3873,'Akron');
|
|
CREATE TABLE t2 (c INT, d VARCHAR(52) PRIMARY KEY, KEY(c)) ENGINE=InnoDB;
|
INSERT INTO t2 VALUES (86,'English'),(87,'Russian');
|
|
--connect (con1,localhost,root,,)
|
--let $con_id = `SELECT CONNECTION_ID()`
|
|
send SELECT SUM(a + SLEEP(0.1)) FROM t1 WHERE a IN ( SELECT c FROM t2 WHERE d < b ) OR b < 's';
|
|
--connection default
|
|
--disable_query_log
|
--disable_abort_on_error
|
--let $run = 1000
|
while ($run)
|
{
|
eval SHOW EXPLAIN FOR $con_id;
|
dec $run;
|
if (!$mysql_errno)
|
{
|
let $run = 0;
|
}
|
}
|
--enable_query_log
|
--enable_abort_on_error
|
--connection con1
|
reap;
|
|
DROP TABLE t1, t2;
|
Below are a couple variations:
See 'col_smallin' – supposed to be 'col_smallint_key'
1 SIMPLE table10000_aria_int_autoinc index_merge PRIMARY,col_varchar_64_key,col_smallint_key,col_bigint_key,col_varchar_10_key col_varchar_10_key,col_varchar_64_key,col_bigint_key,col_smallin 13,67,9,3 8942 Using sort_union(col_varchar_10_key,col_varchar_<rows>_key,col_bigint_key,col_smallint_key); Using where
|
See 'col_varchar_1' – usual EXPLAIN shows 'col_varchar_10_key,PRIMARY':
SIMPLE table10000_innodb_int_autoinc index_merge PRIMARY,col_smallint_key,col_bigint_key,col_varchar_64_key,col_varchar_10_key col_smallint_key,col_bigint_key,col_varchar_64_key,col_varchar_1 3,9,67,13,4 10179 Using sort_union(col_smallint_key,col_bigint_key,col_varchar_64_key,col_varchar_<rows>_key,PRIMARY); Using where
|
|