Speeding Up ORDER BY / GROUP BY with Indexes

Kill the temp sort with an index

When you sort with ORDER BY and the target column has no index, the database has to pull every row out and then sort them.

The plan shows this temporary sort as USE TEMP B-TREE FOR ORDER BY (it builds a temporary working structure just to do the reordering).

Things change once you create an index on the column you're sorting by.

An index is already in the order of that column from the moment it's built.

Walk the index's order and the result drops out immediately.

The temp sort is no longer needed, and USE TEMP B-TREE disappears from the plan.

A temp sort reshuffles all 50,000 rows into a working structure, so the cost grows with the row count.

Even if LIMIT 10 returns only the top 10 rows, picking those top 10 still means reordering every row first — the load doesn't shrink.

In this article, you'll try "killing the sort with an index" in practice.

-- Example: take the top 10 rows ordered by amount
-- With an index, the database just walks its order
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(amount);

EXPLAIN QUERY PLAN
SELECT sale_id, amount
FROM perf_sales
ORDER BY amount
LIMIT 10;

Imagine the requirement: "We want sales sorted by date so we can see the first few." Take the query that sorts by sale_date and compare its plan with and without an index, watching whether USE TEMP B-TREE FOR ORDER BY shows up. Drop and rebuild the index inside this single console execution so the plan is fully self-contained. (Run it correctly to reveal the explanation.)

① First, use DROP INDEX IF EXISTS to drop the index on sale_date, then show EXPLAIN QUERY PLAN for the first 10 rows ordered by sale_date (the no-index state).

② Then create a single-column index on sale_date.

③ Show the plan for the same sort query again, and compare it with ① to see whether the temp sort disappears.

GROUP BY can also lean on the index's order

GROUP BY also needs rows with the same value to sit next to each other so it can group them.

Without an index, that means a temp sort and USE TEMP B-TREE FOR GROUP BY in the plan.

Create an index on the grouping column and rows with the same value are already adjacent in the index's order, so the database can carve out groups just by walking through it.

For example, GROUP BY emp_id paired with an index on emp_id means each emp_id's rows are already consecutive in the index's order, so aggregation runs without a temp sort.

Include the aggregated column in the index too and you also avoid going back to the base table (the Index-Only Scan from the previous article).

-- Example: count per region
-- With an index on region, GROUP BY needs no temp sort
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region);

EXPLAIN QUERY PLAN
SELECT region, COUNT(*)
FROM perf_sales
GROUP BY region;

Imagine the requirement: "We want the sales count per rep." Take the query that groups by emp_id and compare its plan with and without an index, watching whether USE TEMP B-TREE FOR GROUP BY shows up.

① First, use DROP INDEX IF EXISTS to drop the index, then show EXPLAIN QUERY PLAN for the query that counts rows per emp_id (the no-index state).

② Then create a single-column index on emp_id.

③ Show the plan for the same aggregate query again, and compare it with ① to see whether the temp sort disappears.

DISTINCT kills its temp sort the same way

DISTINCT (which drops duplicates and returns only unique values) also needs values lined up to spot the duplicates, so without an index it triggers a temp sort with USE TEMP B-TREE FOR DISTINCT.

The mechanic is the same as GROUP BY: with an index on the target column, identical values are already adjacent in the order, so removing duplicates is just a matter of comparing each neighbor.

For SELECT DISTINCT product FROM perf_sales, where you want the unique values of a single column, an index on product removes the need for a temp sort.

Sorting, grouping, and deduplication all share the trait of being "operations that need values lined up" — and an index on the target column lets you sidestep the temp sort for any of them.

-- Example: get the unique values of region
-- An index on region avoids the DISTINCT temp sort
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region);

EXPLAIN QUERY PLAN
SELECT DISTINCT region
FROM perf_sales;

Imagine the requirement: "We want the list of product codes we've handled, with no duplicates." Take the query that returns the unique values of product and compare its plan with and without an index, watching whether USE TEMP B-TREE FOR DISTINCT shows up.

① First, use DROP INDEX IF EXISTS to drop the index, then show EXPLAIN QUERY PLAN for the query that returns the unique values of product (the no-index state).

② Then create a single-column index on product.

③ Show the plan for the same dedup query again, and compare it with ① to see whether the temp sort disappears.