|
Normal EXPLAIN:
EXPLAIN SELECT COUNT(*) FROM t1,t2,t3 WHERE e>=c AND d!=b;
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 20
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join)
|
1 SIMPLE t3 ALL e NULL NULL NULL 20 Range checked for each record (index map: 0x1)
|
Variations of SHOW EXPLAIN last line during execution of the query:
1 SIMPLE t3 ALL e NULL NULL NULL 20 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 range e e 4 NULL 5 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 range e e 4 NULL 5 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 ALL e NULL NULL NULL 20 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 ALL e NULL NULL NULL 20 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 ALL e NULL NULL NULL 20 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 ALL e NULL NULL NULL 20 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 range e e 4 NULL 4 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 range e e 4 NULL 4 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 ALL e NULL NULL NULL 20 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 ALL e NULL NULL NULL 20 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 ALL e NULL NULL NULL 20 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 ALL e NULL NULL NULL 20 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 ALL e NULL NULL NULL 20 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 ALL e NULL NULL NULL 20 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 ALL e NULL NULL NULL 20 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 ALL e NULL NULL NULL 20 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 range e e 4 NULL 1 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 ALL e NULL NULL NULL 20 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 ALL e NULL NULL NULL 20 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 ALL e NULL NULL NULL 20 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 ALL e NULL NULL NULL 20 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 range e e 4 NULL 2 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 range e e 4 NULL 2 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 ALL e NULL NULL NULL 20 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 ALL e NULL NULL NULL 20 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 ALL e NULL NULL NULL 20 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 range e e 4 NULL 5 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 range e e 4 NULL 5 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 range e e 4 NULL 5 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 ALL e NULL NULL NULL 20 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 ALL e NULL NULL NULL 20 Range checked for each record (index map: 0x1)
|
1 SIMPLE t3 ALL e NULL NULL NULL 20 Range checked for each record (index map: 0x1)
|
bzr version-info
revision-id: psergey@askmonty.org-20120524182239-gq6jtnnovydnthtl
|
date: 2012-05-24 22:22:39 +0400
|
revno: 3405
|
Reproducible with the standard optimizer_switch as well as with all OFF values.
Reproducible with MyISAM, Aria, InnoDB.
COUNT is not required, it just helps to reduce the amount of data (avoid blocking on net reads).
MTR test case:
CREATE TABLE t1 (a INT);
|
INSERT INTO t1 VALUES
|
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
|
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
|
|
CREATE TABLE t2 (b INT, c VARCHAR(1));
|
INSERT INTO t2 VALUES
|
(2,'v'),(1,'v'),(3,'c'),(2,'a'),(5,'x'),(3,'i'),(1,'e'),
|
(4,'p'),(3,'s'),(2,'j'),(6,'z'),(6,'c'),(8,'a'),(2,'q'),
|
(6,'y'),(8,'i'),(3,'r'),(3,'v'),(9,'m'),(6,'r');
|
|
CREATE TABLE t3 (d INT, e VARCHAR(1), KEY(e));
|
INSERT INTO t3 VALUES
|
(2,'x'),(8,'d'),(1,'r'),(9,'f'),(4,'y'),(3,'u'),(2,'m'),
|
(5,'m'),(2,'o'),(2,'w'),(6,'m'),(7,'q'),(2,'m'),(5,'d'),
|
(7,'g'),(6,'x'),(6,'f'),(2,'p'),(9,'j'),(6,'c');
|
|
--connect (con1,localhost,root,,)
|
|
let $con_id = `SELECT CONNECTION_ID()`;
|
|
EXPLAIN SELECT COUNT(*) FROM t1,t2,t3 WHERE e>=c AND d!=b;
|
send SELECT COUNT(*) FROM t1,t2,t3 WHERE e>=c AND d!=b;
|
|
--connection default
|
|
let $run = 40;
|
|
while ($run)
|
{
|
--error 0,ER_ERROR_WHEN_EXECUTING_COMMAND
|
--eval SHOW EXPLAIN FOR $con_id
|
--dec $run
|
}
|
|
--connection con1
|
--reap
|
|