mysql> analyze table t1,t2,t3; +---------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+---------+----------+----------+ | test.t1 | analyze | status | OK | | test.t2 | analyze | status | OK | | test.t3 | analyze | status | OK | +---------+---------+----------+----------+ 3 rows in set (0.01 sec) mysql> mysql> explain select count(*) from t1 straight_join t2 straight_join t3 where t1_id=t2_id and t2_addr=t3_addr ; +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 9317 | 100.00 | NULL | | 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 495779 | 10.00 | Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 38055 | 10.00 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+ 3 rows in set, 1 warning (0.00 sec) mysql> explain analyze select count(*) from t1 straight_join t2 straight_join t3 where t1_id=t2_id and t2_addr=t3_addr ; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | EXPLAIN +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -> Aggregate: count(0) (actual time=1132.752..1132.752 rows=1 loops=1) -> Inner hash join (t3.t3_addr = t2.t2_addr) (cost=1758301330366.13 rows=1757826315846) (actual time=822.237..1131.799 rows=9396 loops=1) -> Table scan on t3 (cost=0.03 rows=38055) (actual time=0.039..15.566 rows=37786 loops=1) -> Hash -> Inner hash join (t2.t2_id = t1.t1_id) (cost=461919825.10 rows=461917301) (actual time=23.364..581.822 rows=9396 loops=1) -> Table scan on t2 (cost=0.70 rows=495779) (actual time=0.035..269.205 rows=497786 loops=1) -> Hash -> Table scan on t1 (cost=939.70 rows=9317) (actual time=0.023..4.822 rows=9396 loops=1) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 row in set (1.17 sec) mysql> flush status; Query OK, 0 rows affected (0.01 sec) mysql> select count(*) from t1 straight_join t2 straight_join t3 where t1_id=t2_id and t2_addr=t3_addr ; +----------+ | count(*) | +----------+ | 9396 | +----------+ 1 row in set (1.15 sec) mysql> show status like 'handler%'; +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 6 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 3 | | Handler_read_key | 3 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 544971 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+--------+ 18 rows in set (0.00 sec) mysql> mysql> explain select count(*) from t1 , t2 ,t3 where t1_id=t2_id and t2_addr=t3_addr ; +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 9317 | 100.00 | NULL | | 1 | SIMPLE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 38055 | 100.00 | Using join buffer (Block Nested Loop) | | 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 495779 | 1.00 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+ 3 rows in set, 1 warning (0.00 sec) mysql> explain analyze select count(*) from t1 , t2 ,t3 where t1_id=t2_id and t2_addr=t3_addr ; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | EXPLAIN +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -> Aggregate: count(0) (actual time=674079.862..674079.862 rows=1 loops=1) -> Inner hash join (t2.t2_addr = t3.t3_addr), (t2.t2_id = t1.t1_id) (cost=17578381389542.80 rows=1757826387878) (actual time=161148.605..674072.347 rows=9396 loops=1) -> Table scan on t2 (cost=0.23 rows=495779) (actual time=0.026..247.703 rows=497786 loops=1) -> Hash -> Inner hash join (cost=35456912.14 rows=354558435) (actual time=17.907..29032.294 rows=355037248 loops=1) -> Table scan on t3 (cost=0.42 rows=38055) (actual time=0.037..80.384 rows=37786 loops=1) -> Hash -> Table scan on t1 (cost=939.70 rows=9317) (actual time=0.018..5.706 rows=9396 loops=1) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 row in set (11 min 15.54 sec) mysql> flush status; Query OK, 0 rows affected (0.00 sec) mysql> select count(*) from t1 , t2 ,t3 where t1_id=t2_id and t2_addr=t3_addr ; +----------+ | count(*) | +----------+ | 9396 | +----------+ 1 row in set (11 min 21.28 sec) mysql> show status like 'handler%'; +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 6 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 3 | | Handler_read_key | 3 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 544971 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+--------+ 18 rows in set (0.00 sec) mysql>