SHOW EXPLAIN: Plan produced by SHOW EXPLAIN changes back and forth during query execution (type=range vs ALL, possible_keys, key, key_len, rows also differ)

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)

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)

revision-id: psergey@askmonty.org-20120524182239-gq6jtnnovydnthtl

date: 2012-05-24 22:22:39 +0400

revno: 3405

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