Replacing MySQL Correlated Scalar Subqueries with Window Functions on InnoDB

A correlated scalar subquery that computes a rank or running total forces MySQL to re-execute the inner query for every outer row. On a large InnoDB table this often appears as a DEPENDENT SUBQUERY join type in EXPLAIN and can multiply logical reads. A window function such as ROW_NUMBER(), RANK(), or SUM() OVER (...) can compute the same value in a single pass when the subquery only depends on grouping and ordering columns. This article walks through the refactor, the checks that matter, and how to roll back safely.

Desired outcome

Replace a correlated scalar subquery with a window function so that the optimizer reads the base table once instead of once per outer row. The target is a query that returns identical logical results but avoids repeated subquery execution, reducing latency on large InnoDB tables.

Prerequisites

• MySQL 8.0 or later; window functions are not available in MySQL 5.7.
• SELECT and PROCESS privileges on the database (required for EXPLAIN and performance_schema inspection).
• A composite index on the columns used in PARTITION BY and ORDER BY to avoid filesorts.
• A staging replica or masked production copy for validation before production rollout.

Locate the correlated subquery

Search application SQL and slow query logs for patterns where a scalar subquery references a column from the outer query. A common shape computes a per-group rank:

SELECT o.order_id,
       (SELECT COUNT(*) + 1
          FROM orders AS inner_o
         WHERE inner_o.customer_id = o.customer_id
           AND inner_o.created_at < o.created_at) AS rank_in_customer
  FROM orders AS o;

Here the inner COUNT repeats for every row in orders. The rewrite uses ROW_NUMBER() partitioned by customer_id and ordered by created_at.

Rewrite with a window function

Replace the scalar subquery with a window expression in the select list:

SELECT order_id,
       ROW_NUMBER() OVER (
         PARTITION BY customer_id
         ORDER BY created_at
       ) AS rank_in_customer
  FROM orders;

For running totals, use SUM() OVER (...) and set the frame explicitly. The default frame is RANGE UNBOUNDED PRECEDING TO CURRENT ROW; if the original logic assumed a different boundary, specify it:

SELECT order_id,
       SUM(amount) OVER (
         PARTITION BY customer_id
         ORDER BY created_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
  FROM orders;

Window functions are evaluated after WHERE and GROUP BY. If the correlated subquery filtered rows before aggregation, a window function alone is not a drop-in replacement; the filter must remain in the outer query or a derived table.

Confirm NULL and frame semantics

Window functions and correlated subqueries can disagree on NULL ordering and peer handling. Verify that the rewritten query places NULLs in the same position as the original. If the original subquery used < comparisons, NULLs are excluded from the count; ROW_NUMBER() still assigns a number to NULL rows. Use NULLS FIRST or NULLS LAST where supported, or adjust the ORDER BY expression to match the legacy behavior.

Validate the execution plan

Run EXPLAIN FORMAT=TREE on both versions. In the MySQL command-line client or any SQL interface with SELECT and PROCESS privileges, execute:

EXPLAIN FORMAT=TREE
SELECT order_id,
       ROW_NUMBER() OVER (
         PARTITION BY customer_id
         ORDER BY created_at
       ) AS rank_in_customer
  FROM orders\G

Look for a single table access on orders rather than a dependent subquery executed per outer row. In traditional EXPLAIN output, check that the key column shows the intended composite index on (customer_id, created_at) and that the Extra column no longer contains DEPENDENT SUBQUERY.

Diff results on masked data

Before production, run both queries against a masked production copy and compare the result sets row by row. A simple approach is to insert both outputs into temporary tables and use a full outer join or a checksum:

-- Run in a staging session with write access to a scratch database
CREATE TEMPORARY TABLE orig_result AS
SELECT o.order_id,
       (SELECT COUNT(*) + 1
          FROM orders AS inner_o
         WHERE inner_o.customer_id = o.customer_id
           AND inner_o.created_at < o.created_at) AS rank_in_customer
  FROM orders AS o;

CREATE TEMPORARY TABLE new_result AS
SELECT order_id,
       ROW_NUMBER() OVER (
         PARTITION BY customer_id
         ORDER BY created_at
       ) AS rank_in_customer
  FROM orders;

-- Compare
SELECT COUNT(*) AS mismatches
  FROM orig_result AS a
  LEFT JOIN new_result AS b USING (order_id)
 WHERE a.rank_in_customer <> b.rank_in_customer
    OR b.order_id IS NULL;

Expected check: the mismatch count is zero for all sampled rows. If mismatches appear, inspect NULL ordering, duplicate created_at values, and frame boundaries before proceeding.

Measure latency under production-like concurrency

After deploying to staging, inspect performance_schema.events_statements_summary_by_digest to compare average latency and execution counts for the query digest. Run a representative workload and confirm the rewritten statement does not increase total I/O or lock waits.

Recovery options

Keep the original SQL in version control. If the window version regresses in production, revert the application change immediately. Drop any test-only indexes created during validation. During the rollback window, monitor the slow query log to ensure the original correlated subquery returns to expected latency and does not saturate connections.

Limitations

• Window functions require MySQL 8.0+; MySQL 5.7 and some MariaDB versions need a different rewrite approach.
• The default frame is RANGE UNBOUNDED PRECEDING TO CURRENT ROW; explicitly set frames for running totals to avoid semantic drift.
• Window functions are evaluated after WHERE and GROUP BY, so they cannot replace subqueries that filter rows before aggregation.
• Without an index on the partition and order columns, the optimizer may add a filesort that offsets the gains from removing the dependent subquery.

Practical verification checklist

1. Run EXPLAIN FORMAT=TREE and confirm no dependent subquery execution.
2. Diff result sets on a masked copy; expect zero mismatches.
3. Check performance_schema.events_statements_summary_by_digest for latency improvement.
4. Verify the key column in EXPLAIN shows the intended composite index.