MariaDB [test]>  explain select * from one_k_key X, ten Y where X.a in (select max(a) from ten);
+----+-------------+-------------+------+---------------+------+---------+--------------------+------+-------------------------------------------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref                | rows | Extra                                           |
+----+-------------+-------------+------+---------------+------+---------+--------------------+------+-------------------------------------------------+
|  1 | PRIMARY     | Y           | ALL  | NULL          | NULL | NULL    | NULL               |   10 |                                                 |
|  1 | PRIMARY     | <subquery2> | ALL  | distinct_key  | NULL | NULL    | NULL               |   10 | Using where; Using join buffer (flat, BNL join) |
|  1 | PRIMARY     | X           | ref  | a             | a    | 5       | <subquery2>.max(a) |    1 | Using index                                     |
|  2 | SUBQUERY    | ten         | ALL  | NULL          | NULL | NULL    | NULL               |   10 |                                                 |
+----+-------------+-------------+------+---------------+------+---------+--------------------+------+-------------------------------------------------+
4 rows in set (0.00 sec)