Skip to content
Curiosity
#sql #optimization #indexing #sargable

SQL execution order for query optimization

The logical order MySQL runs your SELECT — FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY/LIMIT — and the optimization hook each stage gives you.

Updated May 24, 2026
On this page

A SELECT does not run in the order you wrote it. The lexical order is SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT, but the logical evaluation order is closer to FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Knowing which stage your filter or sort happens in tells you which index will actually be used.

The query below is the running example for the rest of the note.

example query
   SELECT  customer_id,
           COUNT(order_id)   AS total_orders,
           SUM(order_amount) AS total_spent
     FROM  customers
     JOIN  orders ON customers.id = orders.customer_id
    WHERE  order_date >= '2023-01-01'
 GROUP BY  customer_id
   HAVING  total_spent >= 1000
 ORDER BY  total_spent DESC
    LIMIT  10;

The diagram below is the logical execution order — rows enter from the source tables at the top and pass through each stage, with row counts collapsing as filters and aggregates drop survivors. Each visual stripe stands for roughly 100 rows of real data, so the numeric labels track realistic table sizes. Press ▶ Play to walk through one stage at a time and read what MySQL actually does at each step.

How MySQL runs your SELECT

Press ▶ Play to watch the engine execute the query top to bottom, one stage at a time. Each stage processes the rows that survived the stage above it.

— / 7
customers (500 rows)orders (800 rows)800rows600rows500rows400rows400rows400rows10rowsresultFROM customersJOIN orders ON c.id = o.cidWHERE order_date >= '2023-01-01'GROUP BY customer_idHAVING total_spent >= 1000SELECT customer_id, total_orders, total_spentORDER BY total_spent DESCLIMIT 10

The diagram shows the seven stages MySQL runs for every SELECT, in the order they actually execute — not the order you wrote them in. Press ▶ Play to walk through one stage at a time, or click any band or clause to jump there.

The six stages

  1. FROM and JOIN — pick tables, pair rows

    The engine resolves the tables and joins them according to the ON predicate. The join cost is dominated by the right-hand table’s lookup strategy: an indexed ON column means an eq_ref / ref probe; an unindexed one degrades to a hash or nested-loop scan. Index the join columns on both sides, and make sure their data types match — a BIGINT joined to a VARCHAR defeats the index.

  2. WHERE — filter rows, keep them SARGable

    The WHERE predicate runs against the joined rows. A predicate is SARGable (Search ARGument ABLE) when it can use an index directly — that requires leaving the indexed column bare on one side of the comparison. Wrap the column in a function (YEAR(x) = 2008) and the index is useless; the engine falls back to a full scan and evaluates the function row-by-row.

  3. GROUP BY — aggregate the surviving rows

    Aggregation runs after filtering. If the GROUP BY columns match a leading prefix of an index, MySQL can stream-aggregate; otherwise it builds a temporary table or sort. Composite indexes that cover both the WHERE columns and the GROUP BY columns avoid the temp table.

  4. HAVING — filter the aggregates

    HAVING filters after GROUP BY. It cannot use a row-level index because the values it filters on (SUM, COUNT, …) only exist post-aggregation. If a predicate doesn’t reference an aggregate, move it to WHERE instead — that drops rows earlier and shrinks the input to GROUP BY.

  5. SELECT — project columns

    The projection runs last among the data-shaping stages. A covering index that contains every column referenced in SELECT, WHERE, and ORDER BY lets the engine answer the query without touching the table data. That’s the difference between Using index (good) and Using index condition followed by row lookups (worse).

  6. ORDER BY and LIMIT — sort and cut

    ORDER BY on an unindexed column triggers Using filesort, which materializes the entire result set in memory or on disk before sorting. An index whose key order matches ORDER BY lets the engine walk the index in order and stop after LIMIT rows — turning a 5M-row sort into 10 reads.

SARGable rewrites

The single most common index-killer is a function or arithmetic on the indexed column. Three concrete rewrites:

Non-SARGable (index unused) 
-- function on the indexed column
SELECT ... WHERE YEAR(my_date) = 2008;

-- substring on the indexed column
SELECT ... WHERE SUBSTRING(dealer_name, 1, 4) = 'Ford';

-- arithmetic on the indexed column
SELECT ... WHERE DATEDIFF(NOW(), order_date) >= 30;
SARGable (index used) 
-- range against the bare column
SELECT ... WHERE my_date >= '2008-01-01'
             AND my_date <  '2009-01-01';

-- prefix match
SELECT ... WHERE dealer_name LIKE 'Ford%';

-- arithmetic moved to the constant side
SELECT ... WHERE order_date < NOW() - INTERVAL 30 DAY;

Where each stage’s wins compound

Axis Without indexes With the right composite index
JOIN Nested loop with full scan of inner table per outer row eq_ref / ref probe — one row per match
WHERE Full table scan, function applied per row Index range scan, function never runs
GROUP BY Builds temp table, then sorts Streams aggregation in index order
ORDER BY + LIMIT Filesort over the entire result set Walks the index, stops after LIMIT rows

What to remember

The execution order is the rulebook; the optimizer rewrites the plan but cannot violate it. Each stage gives you a different optimization hook — index the join column, keep WHERE SARGable, cover the GROUP BY, project from the index, and match ORDER BY to index direction. Confirm what the optimizer actually does with 0007 Reading MySQL EXPLAIN output · MySQL . When the predicate is “does a related row exist?”, reach for 0006 EXISTS instead of JOIN · MySQL ; when it’s “this column in a small set”, see 0008 IN vs OR · MySQL .