mysql>  SET @@optimizer_switch="subquery_to_derived=on"; mysql>  EXPLAIN SELECT * FROM t1 -> WHERE t1.b < 0 -> OR -> EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1)\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: 3 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 1 select_type: PRIMARY table: <derived2> partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 6 filtered: 100.00 Extra: Using where; Using join buffer (hash join) *************************** 3. row *************************** id: 2 select_type: DERIVED table: t2 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 6 filtered: 100.00 Extra: Using temporary SELECT a, b FROM t1 LEFT JOIN (SELECT DISTINCT 1 AS e1, t2.a AS e2 FROM t2) d ON t1.a + 1 = d.e2 WHERE t1.b < 0 OR d.e1 IS NOT NULL; Switchable Optimizations If we execute  SHOW WARNINGS  after running  EXPLAIN  on the query  SELECT * FROM t1 WHERE t1.b < 0 OR EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1)  when subquery_to_derived  has been enabled, and simplify the second row of the result, we see that it has been rewritten in a form which resembles this: For more information, see Section 8.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization” , as well as Section 8.2.1.19, “LIMIT Query Optimization” , and Section 8.2.2.1, “Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations” . 1790