##############################################################################################################################################
#  Top subquery flattened, semi-join execution

SELECT count(*)
FROM (v
         LEFT JOIN c
         ON v.cid = c.cid)
   join c as c2
WHERE v.t >= '2012-01-31 05:00:00'
AND v.t <= '2012-02-08 07:59:59'
AND v.did = '208'
AND c.pid = '3124'
AND v.cid = c2.cid
AND c2.pid = '3124'
AND c2.s = 0
AND
 (
         (
                 c2.cid IN
                 ( /*Inner query 1*/ SELECT v2.cid
                 FROM v2 AS v2
                 WHERE v2.did = 208
                 AND v2.t >= '2012-01-31 05:00:00'
                 AND v2.t <= '2012-02-08 07:59:59'
                 )
         )
 AND
         (
                 c2.cid IN
                 ( /*Inner query 2*/ SELECT v3.cid
                 FROM v3 as v3
                 WHERE v3.did = 208
                 AND v3.t >= '2012-01-31 05:00:00'
                 AND v3.t <= '2012-02-08 07:59:59'
                 )
         )
 AND
         (
                 c2.cid IN
                 ( /*Inner query 3*/ SELECT v4.cid
                 FROM v4 as v4
                 WHERE v4.did = 208
                 AND v4.t >= '2012-01-31 05:00:00'
                 AND v4.t <= '2012-02-08 07:59:59'
                 )
         )
 );
+------+-------------+-------+--------+---------------+---------+---------+------------------------+------+------------------------------------------+
| id   | select_type | table | type   | possible_keys | key     | key_len | ref                    | rows | Extra                                    |
+------+-------------+-------+--------+---------------+---------+---------+------------------------+------+------------------------------------------+
|    1 | PRIMARY     | v2    | index  | cid,did       | cid     | 33      | NULL                   | 1129 | Using where; Using index; LooseScan      |
|    1 | PRIMARY     | c     | eq_ref | PRIMARY       | PRIMARY | 8       | bug929732.v2.cid       |    1 | Using where                              |
|    1 | PRIMARY     | c2    | eq_ref | PRIMARY       | PRIMARY | 8       | bug929732.v2.cid       |    1 | Using where                              |
|    1 | PRIMARY     | v4    | ref    | cid,did       | cid     | 16      | bug929732.v2.cid,const |    1 | Using where; Using index; FirstMatch(c2) |
|    1 | PRIMARY     | v3    | ref    | cid,did       | cid     | 16      | bug929732.v2.cid,const |    1 | Using where; Using index; FirstMatch(v4) |
|    1 | PRIMARY     | v     | ref    | cid,did       | cid     | 16      | bug929732.v2.cid,const |    1 | Using where; Using index                 |
+------+-------------+-------+--------+---------------+---------+---------+------------------------+------+------------------------------------------+

+--------------+------------+-----------+
| Table_schema | Table_name | Rows_read |
+--------------+------------+-----------+
| bug929732    | c2         |       170 |
| bug929732    | v4         |       409 |
| bug929732    | v          |       525 |
| bug929732    | v3         |       409 |
| bug929732    | c          |       170 |
| bug929732    | v2         |      1083 |
+--------------+------------+-----------+
total: 2766
0.04 sec

#############################################################################################################################################
#  Top subquery flattened, non-semi-join execution
+------+--------------+-------+--------+-----------------+---------+---------+-----------------+------+--------------------------+
| id   | select_type  | table | type   | possible_keys   | key     | key_len | ref             | rows | Extra                    |
+------+--------------+-------+--------+-----------------+---------+---------+-----------------+------+--------------------------+
|    1 | PRIMARY      | v     | range  | cid,did         | did     | 17      | NULL            |  275 | Using where; Using index |
|    1 | PRIMARY      | c     | eq_ref | PRIMARY         | PRIMARY | 8       | bug929732.v.cid |    1 | Using where              |
|    1 | PRIMARY      | c2    | eq_ref | PRIMARY         | PRIMARY | 8       | bug929732.v.cid |    1 | Using where              |
|    4 | MATERIALIZED | v4    | range  | PRIMARY,cid,did | did     | 17      | NULL            |  275 | Using where; Using index |
|    3 | MATERIALIZED | v3    | range  | PRIMARY,cid,did | did     | 17      | NULL            |  275 | Using where; Using index |
|    2 | MATERIALIZED | v2    | range  | PRIMARY,cid,did | did     | 17      | NULL            |  275 | Using where; Using index |
+------+--------------+-------+--------+-----------------+---------+---------+-----------------+------+--------------------------+

+--------------+------------+-----------+
| Table_schema | Table_name | Rows_read |
+--------------+------------+-----------+
| bug929732    | v          |       276 |
| bug929732    | c          |       249 |
| bug929732    | c2         |       249 |
| bug929732    | v4         |       276 |
| bug929732    | v3         |       276 |
| bug929732    | v2         |       276 |
+--------------+------------+-----------+
total: 1602
0.03 sec

#############################################################################################################################################
## Now, set optimizer_switch='semijoin=on,loosescan=off':

+------+--------------+-------------+--------+---------------+--------------+---------+------------------------+------+--------------------------+
| id   | select_type  | table       | type   | possible_keys | key          | key_len | ref                    | rows | Extra                    |
+------+--------------+-------------+--------+---------------+--------------+---------+------------------------+------+--------------------------+
|    1 | PRIMARY      | <subquery2> | ALL    | distinct_key  | NULL         | NULL    | NULL                   |  207 |                          |
|    1 | PRIMARY      | c           | eq_ref | PRIMARY       | PRIMARY      | 8       | bug929732.v2.cid       |    1 | Using where              |
|    1 | PRIMARY      | c2          | eq_ref | PRIMARY       | PRIMARY      | 8       | bug929732.v2.cid       |    1 | Using where              |
|    1 | PRIMARY      | <subquery4> | eq_ref | distinct_key  | distinct_key | 8       | func                   |    1 |                          |
|    1 | PRIMARY      | <subquery3> | eq_ref | distinct_key  | distinct_key | 8       | func                   |    1 |                          |
|    1 | PRIMARY      | v           | ref    | cid,did       | cid          | 16      | bug929732.v2.cid,const |    1 | Using where; Using index |
|    2 | MATERIALIZED | v2          | range  | cid,did       | did          | 17      | NULL                   |  275 | Using where; Using index |
|    4 | MATERIALIZED | v4          | range  | cid,did       | did          | 17      | NULL                   |  275 | Using where; Using index |
|    3 | MATERIALIZED | v3          | range  | cid,did       | did          | 17      | NULL                   |  275 | Using where; Using index |
+------+--------------+-------------+--------+---------------+--------------+---------+------------------------+------+--------------------------+

+--------------+------------+-----------+--------------+-------------------------+
| Table_schema | Table_name | Rows_read | Rows_changed | Rows_changed_x_#indexes |
+--------------+------------+-----------+--------------+-------------------------+
| bug929732    | c2         |       170 |            0 |                       0 |
| bug929732    | v4         |       276 |            0 |                       0 |
| bug929732    | v          |       525 |            0 |                       0 |
| bug929732    | v3         |       276 |            0 |                       0 |
| bug929732    | c          |       170 |            0 |                       0 |
| bug929732    | v2         |       276 |            0 |                       0 |
+--------------+------------+-----------+--------------+-------------------------+
total: 1693 
0.03 sec

#############################################################################################################################################
## Explore semi-join plans further: try disabling materialization:

MariaDB [bug929732]> set optimizer_switch='semijoin=on,materialization=off';
+------+-------------+-------+--------+---------------+---------+---------+------------------------+------+------------------------------------------+
| id   | select_type | table | type   | possible_keys | key     | key_len | ref                    | rows | Extra                                    |
+------+-------------+-------+--------+---------------+---------+---------+------------------------+------+------------------------------------------+
|    1 | PRIMARY     | v2    | index  | cid,did       | cid     | 33      | NULL                   | 1344 | Using where; Using index; LooseScan      |
|    1 | PRIMARY     | c     | eq_ref | PRIMARY       | PRIMARY | 8       | bug929732.v2.cid       |    1 | Using where                              |
|    1 | PRIMARY     | c2    | eq_ref | PRIMARY       | PRIMARY | 8       | bug929732.v2.cid       |    1 | Using where                              |
|    1 | PRIMARY     | v4    | ref    | cid,did       | cid     | 16      | bug929732.v2.cid,const |    1 | Using where; Using index; FirstMatch(c2) |
|    1 | PRIMARY     | v3    | ref    | cid,did       | cid     | 16      | bug929732.v2.cid,const |    1 | Using where; Using index; FirstMatch(v4) |
|    1 | PRIMARY     | v     | ref    | cid,did       | cid     | 16      | bug929732.v2.cid,const |    1 | Using where; Using index                 |
+------+-------------+-------+--------+---------------+---------+---------+------------------------+------+------------------------------------------+

+--------------+------------+-----------+--------------+-------------------------+
| Table_schema | Table_name | Rows_read | Rows_changed | Rows_changed_x_#indexes |
+--------------+------------+-----------+--------------+-------------------------+
| bug929732    | c2         |       170 |            0 |                       0 |
| bug929732    | v4         |       409 |            0 |                       0 |
| bug929732    | v          |       525 |            0 |                       0 |
| bug929732    | v3         |       409 |            0 |                       0 |
| bug929732    | c          |       170 |            0 |                       0 |
| bug929732    | v2         |      1083 |            0 |                       0 |
+--------------+------------+-----------+--------------+-------------------------+
total: 2766
0.03 sec


#############################################################################################################################################
#############################################################################################################################################
## The original query, semijoin=on

SELECT count(*)
FROM v
         LEFT JOIN c
         ON v.cid = c.cid
WHERE v.t >= '2012-01-31 05:00:00'
AND v.t <= '2012-02-08 07:59:59'
AND v.did = '208'
AND c.pid = '3124'
AND v.cid IN
         ( SELECT c2.cid
         FROM c2
         WHERE c2.pid = '3124'
         AND c2.s = 0
         AND
                 (
                         (
                                 c2.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'
                                 )
                         )
                 AND
                         (
                                 c2.cid IN
                                 ( /*Inner query 2*/ SELECT v3.cid
                                 FROM v3
                                 WHERE v3.did = 208
                                 AND v3.t >= '2012-01-31 05:00:00'
                                 AND v3.t <= '2012-02-08 07:59:59'
                                 )
                         )
                 AND
                         (
                                 c2.cid IN
                                 ( /*Inner query 3*/ SELECT v4.cid
                                 FROM v4
                                 WHERE v4.did = 208
                                 AND v4.t >= '2012-01-31 05:00:00'
                                 AND v4.t <= '2012-02-08 07:59:59'
                                 )
                         )
                 )
         );

+------+-------------+-------+--------+---------------+---------+---------+-----------------------+------+-------------------------------------------+
| id   | select_type | table | type   | possible_keys | key     | key_len | ref                   | rows | Extra                                     |
+------+-------------+-------+--------+---------------+---------+---------+-----------------------+------+-------------------------------------------+
|    1 | PRIMARY     | v     | range  | cid,did       | did     | 17      | NULL                  |  275 | Using where; Using index                  |
|    1 | PRIMARY     | v2    | ref    | cid,did       | cid     | 16      | bug929732.v.cid,const |    1 | Using where; Using index; Start temporary |
|    1 | PRIMARY     | v3    | ref    | cid,did       | cid     | 16      | bug929732.v.cid,const |    1 | Using where; Using index                  |
|    1 | PRIMARY     | v4    | ref    | cid,did       | cid     | 16      | bug929732.v.cid,const |    1 | Using where; Using index                  |
|    1 | PRIMARY     | c     | eq_ref | PRIMARY       | PRIMARY | 8       | bug929732.v.cid       |    1 | Using where                               |
|    1 | PRIMARY     | c2    | eq_ref | PRIMARY       | PRIMARY | 8       | bug929732.v.cid       |    1 | Using where; End temporary                |
+------+-------------+-------+--------+---------------+---------+---------+-----------------------+------+-------------------------------------------+
1 row in set (6.92 sec)

+--------------+------------+-----------+
| Table_schema | Table_name | Rows_read |
+--------------+------------+-----------+
| bug929732    | v          |       276 | # OK
| bug929732    | v2         |      3994 | # on estimate, should be 276 x 1=276.  Instead, it is 14.5 x times more.
| bug929732    | v3         |     53506 | # again, 13.4 times more
| bug929732    | v4         |    837280 | # 15.6 times more

| bug929732    | c          |       249 |  # Now, we are back to small fanout
| bug929732    | c2         |       249 |  # and here, too
+--------------+------------+-----------+

#############################################################################################################################################
## The original query, semijoin=on, precise index statistics 
## It produces the same query plan 
+------+-------------+-------+--------+---------------+---------+---------+-----------------------+------+-------------------------------------------+
| id   | select_type | table | type   | possible_keys | key     | key_len | ref                   | rows | Extra                                     |
+------+-------------+-------+--------+---------------+---------+---------+-----------------------+------+-------------------------------------------+
|    1 | PRIMARY     | v     | range  | cid,did       | did     | 17      | NULL                  |  275 | Using where; Using index                  |
|    1 | PRIMARY     | c     | eq_ref | PRIMARY       | PRIMARY | 8       | bug929732.v.cid       |    1 | Using where                               |
|    1 | PRIMARY     | c2    | eq_ref | PRIMARY       | PRIMARY | 8       | bug929732.v.cid       |    1 | Using where                               |
|    1 | PRIMARY     | v4    | ref    | cid,did       | cid     | 16      | bug929732.v.cid,const |    1 | Using where; Using index; Start temporary |
|    1 | PRIMARY     | v2    | ref    | cid,did       | cid     | 16      | bug929732.v.cid,const |    1 | Using where; Using index                  |
|    1 | PRIMARY     | v3    | ref    | cid,did       | cid     | 16      | bug929732.v.cid,const |    1 | Using where; Using index; End temporary   |
+------+-------------+-------+--------+---------------+---------+---------+-----------------------+------+-------------------------------------------+

# and execution enumerates lots of rows, too:
+--------------+-------------+-----------+
| Table_schema | Table_name  | Rows_read |
+--------------+-------------+-----------+
| bug929732    | v           |       276 |
| bug929732    | c           |       249 |
| bug929732    | c2          |       249 |

| bug929732    | v4          |      3994 |
| bug929732    | v2          |     53506 |
| bug929732    | v3          |    837280 |
+--------------+-------------+-----------+

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


#############################################################################################################################################
# Maybe, the data is skewed.

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'
   ;
## 1145 rows:  average subquerys fanout is about 4x.

+--------------+------------+-----------+
| Table_schema | Table_name | Rows_read |
+--------------+------------+-----------+
| bug929732    | v          |       276 |
| bug929732    | v2         |      3994 |
+--------------+------------+-----------+