Subquery optimization

Scenario 1: Eliminate redundant SELECT nesting

When it applies: The subquery wraps only an aggregate function or expression with no other complex logic.

Rewrite pattern:

-- Before
SELECT (SELECT SUM(a) FROM t2) FROM dual;

-- After
SELECT SUM(`test`.`t2`.`a`) AS `sum(a)` FROM `test`.`t2`

The same elimination applies in HAVING clauses, where the optimizer flattens multiple layers of nesting:

-- Before
SELECT SUM(a) FROM t2 HAVING (SELECT(SELECT(SELECT count(b))));

-- After
SELECT SUM(`testdb`.`t2`.`a`) AS `SUM(a)` from `testdb`.`t2` HAVING (0 <> count(`testdb`.`t2`.`b`))

Verify with an example:

1. Prepare test data:

   DROP TABLE IF EXISTS t2;
   CREATE TABLE t2 (
       id INT PRIMARY KEY AUTO_INCREMENT,
       a INT,
       b INT
   );
   INSERT INTO t2 (a, b) VALUES (10, 100), (20, NULL), (50, 200), (120, NULL);

2. Check the execution plan without optimization:

   SET simplify_subq_mode = 'OFF';
   EXPLAIN SELECT SUM(a) FROM t2 HAVING (SELECT(SELECT(SELECT count(b))));
   SHOW WARNINGS;

   Result — four nested SELECT layers remain:

   /* select#1 */ select sum(`testdb`.`t2`.`a`) AS `SUM(a)` from `testdb`.`t2` having (0 <> (/* select#2 */ select (/* select#3 */ select (/* select#4 */ select count(`testdb`.`t2`.`b`)))))

3. Check the execution plan with optimization enabled:

   SET simplify_subq_mode = 'ON';
   EXPLAIN SELECT SUM(a) FROM t2 HAVING (SELECT(SELECT(SELECT count(b))));
   SHOW WARNINGS;

   Result — all nesting is eliminated:

   /* select#1 */ select sum(`testdb`.`t2`.`a`) AS `SUM(a)` from `testdb`.`t2` having (0 <> count(`testdb`.`t2`.`b`))

Scenario 2: Pre-evaluate [NOT] EXISTS subqueries

When it applies: The optimizer can statically determine that an EXISTS or NOT EXISTS subquery always returns true (non-empty) or false (empty).

Rewrite pattern:

-- Non-empty result: EXISTS clause is removed entirely
-- Before
SELECT * FROM t1 WHERE EXISTS(SELECT MAX(a) FROM t2);
-- After
SELECT * FROM t1

-- Empty result (WHERE/HAVING evaluates to false, or LIMIT 0): replaced with false
-- Before
SELECT * FROM t1 WHERE EXISTS(SELECT max(a) FROM t2 HAVING 1=2);
-- After
SELECT * FROM t1 WHERE false

This prevents the subquery from executing at all.

Verify with an example:

1. Prepare test data:

   DROP TABLE IF EXISTS t1;
   DROP TABLE IF EXISTS t2;
   CREATE TABLE t1 (id INT);
   CREATE TABLE t2 (val INT);
   INSERT INTO t1 VALUES (1), (2);

2. Check the execution plan without optimization:

   SET simplify_subq_mode = 'OFF';
   EXPLAIN SELECT * FROM t1 WHERE EXISTS(SELECT MAX(a) FROM t2);
   SHOW WARNINGS;

   Result — the EXISTS subquery is evaluated at runtime:

   /* select#1 */ select `testdb`.`t1`.`id` AS `id` from `testdb`.`t1` where exists(/* select#2 */ select max(`testdb`.`t1`.`id`) from `testdb`.`t2`)

3. Check the execution plan with optimization enabled:

   SET simplify_subq_mode = 'ON';
   EXPLAIN SELECT * FROM t1 WHERE EXISTS(SELECT MAX(a) FROM t2);
   SHOW WARNINGS;

   Result — the EXISTS clause is gone:

   /* select#1 */ select `testdb`.`t1`.`id` AS `id` from `testdb`.`t1`

Scenario 3: Add LIMIT 1 to constant projections in ANY/ALL subqueries

When it applies: The subquery inside an ANY or ALL clause selects only a constant (no column references). Retrieving multiple identical rows is wasteful, so the optimizer injects LIMIT 1.

Rewrite pattern:

-- Before
SELECT * FROM t1 WHERE a > ANY (SELECT 1 FROM t2);

-- After
SELECT * FROM t1 WHERE a > ANY (SELECT 1 FROM t2 LIMIT 1);

This avoids a full table scan on t2 when the constant result is the same for every row.