create table ten(a int);

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 t1 (a int, b int, c int, key(a));

create table t10 like t1;

insert into t10 select A.a +1000*B.a, A.a +1000*B.a,A.a +1000*B.a from one_k A, one_k B;

analyze table ten;

analyze table t10;

MariaDB [test]>  set join_cache_level=2;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> 

MariaDB [test]> explain select * from ten, t10 where t10.a=ten.a;

+------+-------------+-------+------+---------------+------+---------+------------+------+-------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref        | rows | Extra       |

+------+-------------+-------+------+---------------+------+---------+------------+------+-------------+

|    1 | SIMPLE      | ten   | ALL  | NULL          | NULL | NULL    | NULL       |   10 | Using where |

|    1 | SIMPLE      | t10   | ref  | a             | a    | 5       | test.ten.a |    1 |             |

+------+-------------+-------+------+---------------+------+---------+------------+------+-------------+

2 rows in set (0.001 sec)

MariaDB [test]>  set join_cache_level=4;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> explain select * from ten, t10 where t10.a=ten.a;

+------+-------------+-------+----------+---------------+---------+---------+------------+--------+-------------------------------------+

| id   | select_type | table | type     | possible_keys | key     | key_len | ref        | rows   | Extra                               |

+------+-------------+-------+----------+---------------+---------+---------+------------+--------+-------------------------------------+

|    1 | SIMPLE      | ten   | ALL      | NULL          | NULL    | NULL    | NULL       |     10 | Using where                         |

|    1 | SIMPLE      | t10   | hash_ALL | a             | #hash#a | 5       | test.ten.a | 997980 | Using join buffer (flat, BNLH join) |

+------+-------------+-------+----------+---------------+---------+---------+------------+--------+-------------------------------------+

2 rows in set (0.001 sec)