MariaDB [test]> set @@join_cache_level=3; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> explain extended select * from t1,t10 where t10.c=t1.a; +------+-------------+-------+----------+---------------+-----------+---------+-----------+-------+----------+--------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+----------+---------------+-----------+---------+-----------+-------+----------+--------------------------------------------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 9 | 100.00 | Using where | | 1 | SIMPLE | t10 | hash_ALL | NULL | #hash#$hj | 5 | test.t1.a | 10023 | 10.00 | Using where; Using join buffer (flat, BNLH join) | +------+-------------+-------+----------+---------------+-----------+---------+-----------+-------+----------+--------------------------------------------------+ 2 rows in set, 1 warning (0.002 sec) Note (Code 1003): select `test`.`t1`.`a` AS `a`,`test`.`t10`.`a` AS `a`,`test`.`t10`.`b` AS `b`,`test`.`t10`.`filler` AS `filler`,`test`.`t10`.`c` AS `c` from `test`.`t1` join `test`.`t10` where `test`.`t10`.`c` = `test`.`t1`.`a` MariaDB [test]> \r Connection id: 13 Current database: test MariaDB [test]> set @@join_cache_level=default; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> explain extended select /*+BNL(t10) */ * from t1,t10 where t10.c=t1.a; +------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------------------------------------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 9 | 100.00 | | | 1 | SIMPLE | t10 | ALL | NULL | NULL | NULL | NULL | 10023 | 100.00 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------------------------------------------+ 2 rows in set, 1 warning (0.001 sec) Note (Code 1003): select /*+ BNL(`t10`@`select#1`) */ `test`.`t1`.`a` AS `a`,`test`.`t10`.`a` AS `a`,`test`.`t10`.`b` AS `b`,`test`.`t10`.`filler` AS `filler`,`test`.`t10`.`c` AS `c` from `test`.`t1` join `test`.`t10` where `test`.`t10`.`c` = `test`.`t1`.`a` In MySQL: create table ten(a int primary key); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table one_k(a int primary key); insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; create table t10( a int, b int, c int, filler char(100), index(a) ); insert into t10 select A.a+1000*B.a, A.a+1000*B.a, A.a+1000*B.a, A.a+1000*B.a from one_k A, ten B; create table t1 as select a from ten; analyze table t1, t10; explain select * from t1, t10 where t1.a=t10.c; mysql> explain select * from t1, t10 where t1.a=t10.c; +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL | | 1 | SIMPLE | t10 | NULL | ALL | NULL | NULL | NULL | NULL | 10044 | 10.00 | Using where; Using join buffer (hash join) | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+ 2 rows in set, 1 warning (0,00 sec) mysql> set optimizer_switch='block_nested_loop=off'; Query OK, 0 rows affected (0,00 sec) mysql> explain select * from t1, t10 where t1.a=t10.c; +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL | | 1 | SIMPLE | t10 | NULL | ALL | NULL | NULL | NULL | NULL | 10044 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 2 rows in set, 1 warning (0,01 sec) mysql> explain select /*+ BNL(t10) */ * from t1, t10 where t1.a=t10.c; +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL | | 1 | SIMPLE | t10 | NULL | ALL | NULL | NULL | NULL | NULL | 10044 | 10.00 | Using where; Using join buffer (hash join) | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+ 2 rows in set, 1 warning (0,00 sec)