Let's analyze a simpler query: SELECT MAX(alias2.a) FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); If subqueries are not expensive (5.2, or changed 5.3) the query is optimized as the original test case, with the only difference, that optimize_cond is run for the WHERE clause, and the WHERE clause is transformed into: "alias1.a = alias2.a". This allows the JOIN optimizer to find a good plan that takes 0.02 sec: +----+--------------------+--------+----------------+---------------+------+---------+--------------------+------+----------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+--------+----------------+---------------+------+---------+--------------------+------+----------+-------------------------------------------------+ | 1 | PRIMARY | alias1 | index | a | a | 19 | NULL | 133 | 100.00 | Using where; Using index | | 1 | PRIMARY | alias2 | ref | a | a | 19 | lpb944706.alias1.a | 1 | 100.00 | Using index | | 1 | PRIMARY | alias3 | index | NULL | a | 19 | NULL | 133 | 100.00 | Using index; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | t1 | index_subquery | a | a | 19 | const | 10 | 100.00 | Using index; Using where | +----+--------------------+--------+----------------+---------------+------+---------+--------------------+------+----------+-------------------------------------------------+ In 5.3 where subqueries are expensive, the IN cannot be optimized away, and the OR condition doesn't allow a plan with ref access. The resulting plan takes ~ 3 sec: +----+--------------------+--------+----------------+---------------+------+---------+-------+------+----------+---------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+--------+----------------+---------------+------+---------+-------+------+----------+---------------------------------------------------------------------+ | 1 | PRIMARY | alias3 | index | NULL | a | 19 | NULL | 133 | 100.00 | Using index | | 1 | PRIMARY | alias2 | index | a | a | 19 | NULL | 133 | 100.00 | Using index; Using join buffer (flat, BNL join) | | 1 | PRIMARY | alias1 | index | a | a | 19 | NULL | 133 | 100.00 | Using where; Using index; Using join buffer (incremental, BNL join) | | 2 | DEPENDENT SUBQUERY | t1 | index_subquery | a | a | 19 | const | 10 | 100.00 | Using index; Using where | +----+--------------------+--------+----------------+---------------+------+---------+-------+------+----------+---------------------------------------------------------------------+ Since there is subquery caching, the subquery itself is executed only once, but the function that tests the cache is called for each join row. Finally, as in the original query the above plan can be improved by setting join_cache_level=0 down to 0.07 sec: +----+--------------+--------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+--------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | PRIMARY | alias1 | index | a | a | 19 | NULL | 133 | 100.00 | Using index | | 1 | PRIMARY | alias2 | index | a | a | 19 | NULL | 133 | 100.00 | Using where; Using index | | 1 | PRIMARY | alias3 | index | NULL | a | 19 | NULL | 133 | 100.00 | Using index | | 2 | MATERIALIZED | t1 | index | a | a | 19 | NULL | 133 | 100.00 | Using index | +----+--------------+--------+-------+---------------+------+---------+------+------+----------+--------------------------+