## For full scan on table v2, EXPLAIN gives rows=1083, which gives
rec_per_key=1.8. EXPLAIN shows '1'.
Checking how many records subquery with table v2 has for table v:
select count(*) from v where
v.t >= '2012-01-31 05:00:00'
AND v.t <= '2012-02-08 07:59:59'
AND v.did = '208';
# This gives 275 rows, which confirms the previous findings
select count(*) from v where
v.t >= '2012-01-31 05:00:00'
AND v.t <= '2012-02-08 07:59:59'
AND v.did = '208'
and v.cid IN
( /*Inner query 1*/ SELECT v2.cid
FROM v2
WHERE v2.did = 208
AND v2.t >= '2012-01-31 05:00:00'
AND v2.t <= '2012-02-08 07:59:59'
);
## ^^ 275 rows again: the subquery has a match for each row of table v.
select count(*) from v,v2 where
v.t >= '2012-01-31 05:00:00'
AND v.t <= '2012-02-08 07:59:59'
AND v.did = '208'
and v.cid = v2.cid and
v2.did = 208
AND v2.t >= '2012-01-31 05:00:00'
AND v2.t <= '2012-02-08 07:59:59'
;
(output is count(*)=1145)
## Lets check index statistics: -+----- ------- +------ ----+-- ------- -----+- ------- -----+- ------- ---+--- ------- ---+ -+----- ------- +------ ----+-- ------- -----+- ------- -----+- ------- ---+--- ------- ---+
+------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality |
+------
| v2 | 1 | cid | 1 | cid | A | 589 |
| v2 | 1 | cid | 2 | did | A | 589 |
## For full scan on table v2, EXPLAIN gives rows=1083, which gives
rec_per_key=1.8. EXPLAIN shows '1'.
Checking how many records subquery with table v2 has for table v:
select count(*) from v where
v.t >= '2012-01-31 05:00:00'
AND v.t <= '2012-02-08 07:59:59'
AND v.did = '208';
# This gives 275 rows, which confirms the previous findings
select count(*) from v where
v.t >= '2012-01-31 05:00:00'
AND v.t <= '2012-02-08 07:59:59'
AND v.did = '208'
and v.cid IN
( /*Inner query 1*/ SELECT v2.cid
FROM v2
WHERE v2.did = 208
AND v2.t >= '2012-01-31 05:00:00'
AND v2.t <= '2012-02-08 07:59:59'
);
## ^^ 275 rows again: the subquery has a match for each row of table v.
select count(*) from v,v2 where
v.t >= '2012-01-31 05:00:00'
AND v.t <= '2012-02-08 07:59:59'
AND v.did = '208'
and v.cid = v2.cid and
v2.did = 208
AND v2.t >= '2012-01-31 05:00:00'
AND v2.t <= '2012-02-08 07:59:59'
;
(output is count(*)=1145)
+------ ------- -+----- ------- +------ -----+ ------- -+----- ------- +------ -----+ ------- -+----- ------- +------ -----+
| Table_schema | Table_name | Rows_read |
+------
| bug929732 | v | 276 |
| bug929732 | v2 | 3994 |
+------
## This gives: total subquery's fanout is 1145/276= 4x,
## the number of records that one needs to read is 15x.