Query performance

01The problem

Your SQL runs in a second on Harbourline's 2,683 shipments. At your next job, the shipments table has 80 million rows, and the same query runs for twenty minutes, then times out and holds up everyone else's reports.

The data volume is out of your hands, but how you write the query isn't. The difference between a query that searches an index and one that scans every row can be a thousandfold at scale. Analysts who write fast queries get their numbers sooner and are trusted with access to bigger systems.

02The concept

Scan or search

To find rows, a database either scans (reads every row in the table) or searches (uses an index to jump straight to the rows it needs). An index is like the index at the back of a book: a sorted list of values, each pointing to where the matching rows are. Searching 80 million rows through an index takes a few steps, while scanning them means reading all 80 million.

Reading the plan

EXPLAIN QUERY PLAN (SQLite), EXPLAIN (PostgreSQL, MySQL) or the "estimated execution plan" (SQL Server) shows what the database intends to do, without running the query. Look for:

Habits that keep queries fast

1. Keep filters sargable: leave the column bare. WHERE strftime('%Y', booking_date) = '2026' has to calculate the year for every row, so it can't use an index on booking_date. WHERE booking_date >= '2026-01-01' AND booking_date < '2027-01-01' can.
2. Select only the columns you need. SELECT * reads and sends everything, and stops covering indexes from working.
3. Aggregate before you join. Total payments per shipment first, then join 2,409 rows down to 2,250, rather than joining everything and grouping at the end.
4. Avoid correlated subqueries in SELECT on big tables. Replace them with a join to a pre-aggregated CTE.
5. Prefer UNION ALL to UNION when duplicates are impossible or wanted. UNION has to sort everything to remove them.
6. Beware leading wildcards. LIKE '%Foods' can't use an index; LIKE 'Kings%' can.

Indexes aren't free: each one slows down inserts and updates and takes space. In most jobs analysts don't create indexes on production databases themselves; they show the plan to a data engineer or DBA and ask. Writing sargable queries is always in your hands.

03Example

Harbourline's practice database has no indexes, so every query scans:

EXPLAIN QUERY PLAN
SELECT * FROM shipments WHERE customer_id = 42;

SCAN shipments. Now create an index and ask again. Your browser has its own copy of the database, so this changes nothing for anyone else:

CREATE INDEX IF NOT EXISTS idx_shipments_customer ON shipments(customer_id);
EXPLAIN QUERY PLAN
SELECT * FROM shipments WHERE customer_id = 42;

SEARCH shipments USING INDEX idx_shipments_customer (customer_id=?). The database now jumps straight to customer 42's rows.

04Walkthrough

1. Create an index on booking dates, and compare the plans of the two ways of filtering on a year:

CREATE INDEX IF NOT EXISTS idx_shipments_booking ON shipments(booking_date);
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM shipments WHERE strftime('%Y', booking_date) = '2026';

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM shipments WHERE booking_date >= '2026-01-01' AND booking_date < '2027-01-01';

2. Read the two plans. The first still scans (the whole index, calculating the year for every entry). The second searches a range of the index: booking_date>? AND booking_date<?. Same answer, very different work at scale.
3. Look at the plan for a correlated subquery:

EXPLAIN QUERY PLAN
SELECT
  c.company_name,
  (SELECT SUM(p.amount)
   FROM payments AS p
   JOIN shipments AS s ON s.shipment_id = p.shipment_id
   WHERE s.customer_id = c.customer_id) AS total_paid
FROM customers AS c;

4. Find CORRELATED SCALAR SUBQUERY in the plan: the subquery runs once for each of the 120 customers. With a million customers it would run a million times. The practice tasks below rewrite it.

05Practice

06Challenge

07More practice

08Check your understanding