rendered paste body--------------
SELECT DISTINCT D1t.id FROM D1t JOIN D2t USING (id) JOIN D3t USING (id) JOIN D4t USING (id) JOIN D5t USING (id)
--------------
+---------+
| id |
+---------+
| 886657 |
| 2598954 |
..... deleted records for space
| 2598955 |
| 2598956 |
| 2598957 |
| 2598958 |
| 2598959 |
| 2598960 |
+---------+
1712304 rows in set (1 min 4.75 sec)
--------------
EXPLAIN SELECT DISTINCT D1t.id FROM D1t JOIN D2t USING (id) JOIN D3t USING (id) JOIN D4t USING (id) JOIN D5t USING (id)
--------------
+----+-------------+-------+--------+---------------+---------+---------+---------------------+---------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------------+---------+------------------------------------+
| 1 | SIMPLE | D1t | index | PRIMARY | PRIMARY | 4 | NULL | 2598960 | Using index; Using temporary |
| 1 | SIMPLE | D2t | eq_ref | PRIMARY | PRIMARY | 4 | payline_data.D1t.id | 1 | Using index; Distinct |
| 1 | SIMPLE | D3t | eq_ref | PRIMARY | PRIMARY | 4 | payline_data.D2t.id | 1 | Using where; Using index; Distinct |
| 1 | SIMPLE | D4t | eq_ref | PRIMARY | PRIMARY | 4 | payline_data.D1t.id | 1 | Using index; Distinct |
| 1 | SIMPLE | D5t | eq_ref | PRIMARY | PRIMARY | 4 | payline_data.D2t.id | 1 | Using where; Using index; Distinct |
+----+-------------+-------+--------+---------------+---------+---------+---------------------+---------+------------------------------------+
5 rows in set (0.00 sec)
--------------
SELECT count(*) as count, sum(vp) as vp_total, sum(hp) as hp_total from hands where id IN (SELECT DISTINCT D1t.id FROM D1t JOIN D2t USING (id) JOIN D3t USING (id) JOIN D4t USING (id) JOIN D5t USING (id))
--------------
+---------+-----------------+----------+
| count | vp_total | hp_total |
+---------+-----------------+----------+
| 1712304 | 33894283.552085 | 364228 |
+---------+-----------------+----------+
1 row in set (46.35 sec)
--------------
EXPLAIN SELECT count(*) as count, sum(vp) as vp_total, sum(hp) as hp_total from hands where id IN (SELECT DISTINCT D1t.id FROM D1t JOIN D2t USING (id) JOIN D3t USING (id) JOIN D4t USING (id) JOIN D5t USING (id))
--------------
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+-------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+-------------------------------------------+
| 1 | PRIMARY | hands | ALL | NULL | NULL | NULL | NULL | 2869685 | Using where |
| 2 | DEPENDENT SUBQUERY | D1t | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where; Using index; Using temporary |
| 2 | DEPENDENT SUBQUERY | D2t | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | D3t | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | D4t | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | D5t | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where; Using index |
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+-------------------------------------------+
6 rows in set (0.03 sec)
--------------
SELECT D1t.id FROM D1t JOIN D2t USING (id) JOIN D3t USING (id) JOIN D4t USING (id) JOIN D5t USING (id)
--------------
+---------+
| id |
+---------+
| 886657 |
| 886658 |
..... deleted rows for space
| 2598958 |
| 2598959 |
| 2598960 |
+---------+
1712304 rows in set (26.70 sec)
--------------
EXPLAIN SELECT D1t.id FROM D1t JOIN D2t USING (id) JOIN D3t USING (id) JOIN D4t USING (id) JOIN D5t USING (id)
--------------
+----+-------------+-------+--------+---------------+---------+---------+---------------------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------------+---------+--------------------------+
| 1 | SIMPLE | D1t | index | PRIMARY | PRIMARY | 4 | NULL | 2598960 | Using index |
| 1 | SIMPLE | D2t | eq_ref | PRIMARY | PRIMARY | 4 | payline_data.D1t.id | 1 | Using index |
| 1 | SIMPLE | D3t | eq_ref | PRIMARY | PRIMARY | 4 | payline_data.D2t.id | 1 | Using where; Using index |
| 1 | SIMPLE | D4t | eq_ref | PRIMARY | PRIMARY | 4 | payline_data.D1t.id | 1 | Using index |
| 1 | SIMPLE | D5t | eq_ref | PRIMARY | PRIMARY | 4 | payline_data.D2t.id | 1 | Using where; Using index |
+----+-------------+-------+--------+---------------+---------+---------+---------------------+---------+--------------------------+
5 rows in set (0.00 sec)
--------------
SELECT count(*) as count, sum(vp) as vp_total, sum(hp) as hp_total from hands where id IN (SELECT D1t.id FROM D1t JOIN D2t USING (id) JOIN D3t USING (id) JOIN D4t USING (id) JOIN D5t USING (id))
--------------
+---------+-----------------+----------+
| count | vp_total | hp_total |
+---------+-----------------+----------+
| 1712304 | 33894283.552085 | 364228 |
+---------+-----------------+----------+
1 row in set (37.45 sec)
--------------
EXPLAIN SELECT count(*) as count, sum(vp) as vp_total, sum(hp) as hp_total from hands where id IN (SELECT D1t.id FROM D1t JOIN D2t USING (id) JOIN D3t USING (id) JOIN D4t USING (id) JOIN D5t USING (id))
--------------
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+--------------------------+
| 1 | PRIMARY | hands | ALL | NULL | NULL | NULL | NULL | 2869685 | Using where |
| 2 | DEPENDENT SUBQUERY | D1t | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | D2t | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | D3t | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | D4t | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | D5t | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where; Using index |
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+--------------------------+
6 rows in set (0.00 sec)
--------------
SELECT count(*) as count, sum(hands.vp) as vp_total, sum(hands.hp) as hp_total from hands, D1t, D2t, D3t, D4t, D5t where hands.id = D1t.id AND hands.id = D2t.id AND hands.id = D3t.id AND hands.id = D4t.id AND hands.id = D5t.id
--------------
+---------+-----------------+----------+
| count | vp_total | hp_total |
+---------+-----------------+----------+
| 1712304 | 33894283.552085 | 364228 |
+---------+-----------------+----------+
1 row in set (33.28 sec)
--------------
EXPLAIN SELECT count(*) as count, sum(hands.vp) as vp_total, sum(hands.hp) as hp_total from hands, D1t, D2t, D3t, D4t, D5t where hands.id = D1t.id AND hands.id = D2t.id AND hands.id = D3t.id AND hands.id = D4t.id AND hands.id = D5t.id
--------------
+----+-------------+-------+--------+---------------+---------+---------+---------------------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------------+---------+--------------------------+
| 1 | SIMPLE | D1t | index | PRIMARY | PRIMARY | 4 | NULL | 2598960 | Using index |
| 1 | SIMPLE | D2t | eq_ref | PRIMARY | PRIMARY | 4 | payline_data.D1t.id | 1 | Using index |
| 1 | SIMPLE | D3t | eq_ref | PRIMARY | PRIMARY | 4 | payline_data.D2t.id | 1 | Using where; Using index |
| 1 | SIMPLE | D4t | eq_ref | PRIMARY | PRIMARY | 4 | payline_data.D3t.id | 1 | Using where; Using index |
| 1 | SIMPLE | D5t | eq_ref | PRIMARY | PRIMARY | 4 | payline_data.D1t.id | 1 | Using index |
| 1 | SIMPLE | hands | eq_ref | PRIMARY | PRIMARY | 4 | payline_data.D1t.id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+---------------------+---------+--------------------------+
6 rows in set (0.00 sec)
--------------
SELECT count(*) as count, sum(vp) as vp_total, sum(hp) as hp_total from hands where (c1 <> 1 and c2 <> 1 and c3 <> 1 and c4 <> 1 and c5 <> 1) and (c1 <> 2 and c2 <> 2 and c3 <> 2 and c4 <> 2 and c5 <> 2) and (c1 <> 3 and c2 <> 3 and c3 <> 3 and c4 <> 3 and c5 <> 3) and (c1 <> 4 and c2 <> 4 and c3 <> 4 and c4 <> 4 and c5 <> 4) and (c1 <> 5 and c2 <> 5 and c3 <> 5 and c4 <> 5 and c5 <> 5)
--------------
+---------+-----------------+----------+
| count | vp_total | hp_total |
+---------+-----------------+----------+
| 1712304 | 33894283.552085 | 364228 |
+---------+-----------------+----------+
1 row in set (2.53 sec)
--------------
EXPLAIN SELECT count(*) as count, sum(vp) as vp_total, sum(hp) as hp_total from hands where (c1 <> 1 and c2 <> 1 and c3 <> 1 and c4 <> 1 and c5 <> 1) and (c1 <> 2 and c2 <> 2 and c3 <> 2 and c4 <> 2 and c5 <> 2) and (c1 <> 3 and c2 <> 3 and c3 <> 3 and c4 <> 3 and c5 <> 3) and (c1 <> 4 and c2 <> 4 and c3 <> 4 and c4 <> 4 and c5 <> 4) and (c1 <> 5 and c2 <> 5 and c3 <> 5 and c4 <> 5 and c5 <> 5)
--------------
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | hands | ALL | NULL | NULL | NULL | NULL | 2869685 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
Bye