Comment 13 for bug 929732

Revision history for this message
Sergey Petrunia (sergefp) wrote :

## 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.