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