
MariaDB [test]> create table ten(a int primary key);
Query OK, 0 rows affected (0.010 sec)

MariaDB [test]> insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);


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  | PRIMARY       | NULL | NULL    | NULL       | 10   |       |
|    1 | SIMPLE      | t10   | ref  | a             | a    | 5       | test.ten.a | 1    |       |
+------+-------------+-------+------+---------------+------+---------+------------+------+-------+
2 rows in set (0.002 sec)

MariaDB [test]> set join_cache_level=6;
Query OK, 0 rows affected (0.000 sec)

## Alone, this doesnt help:

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  | PRIMARY       | NULL | NULL    | NULL       | 10   |       |
|    1 | SIMPLE      | t10   | ref  | a             | a    | 5       | test.ten.a | 1    |       |
+------+-------------+-------+------+---------------+------+---------+------------+------+-------+
2 rows in set (0.002 sec)

## One can enable MRR through optimizer_switch:
MariaDB [test]> set optimizer_switch='mrr=on';
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  | PRIMARY       | NULL | NULL    | NULL       | 10   |                                                        |
|    1 | SIMPLE      | t10   | ref  | a             | a    | 5       | test.ten.a | 1    | Using join buffer (flat, BKA join); Rowid-ordered scan |
+------+-------------+-------+------+---------------+------+---------+------------+------+--------------------------------------------------------+
2 rows in set (0.000 sec)

### Or through a hint:

MariaDB [test]> set optimizer_switch='mrr=off';

MariaDB [test]> explain select /*+MRR(t10 a) */ * 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  | PRIMARY       | NULL | NULL    | NULL       | 10   |                                                        |
|    1 | SIMPLE      | t10   | ref  | a             | a    | 5       | test.ten.a | 1    | Using join buffer (flat, BKA join); Rowid-ordered scan |
+------+-------------+-------+------+---------------+------+---------+------------+------+--------------------------------------------------------+
2 rows in set (0.001 sec)

