+------+ | a | +------+ | 3 | | 4 | +------+ mysql>  SELECT @@optimizer_switch LIKE ’%subquery_to_derived=off%’; +-----------------------------------------------------+ | @@optimizer_switch LIKE ’%subquery_to_derived=off%’ | +-----------------------------------------------------+ | 1 | +-----------------------------------------------------+ mysql>  EXPLAIN SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2)\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 filtered: 33.33 Extra: Using where *************************** 2. row *************************** id: 2 select_type: SUBQUERY table: t2 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 filtered: 100.00 Extra: NULL mysql>  SET @@optimizer_switch=’subquery_to_derived=on’; mysql>  SELECT @@optimizer_switch LIKE ’%subquery_to_derived=off%’; +-----------------------------------------------------+ | @@optimizer_switch LIKE ’%subquery_to_derived=off%’ | +-----------------------------------------------------+ | 0 | +-----------------------------------------------------+ mysql>  SELECT @@optimizer_switch LIKE ’%subquery_to_derived=on%’; +----------------------------------------------------+ | @@optimizer_switch LIKE ’%subquery_to_derived=on%’ | +----------------------------------------------------+ | 1 | +----------------------------------------------------+ mysql>  EXPLAIN SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2)\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> partitions: NULL type: ALL possible_keys: NULL 1786 Switchable Optimizations