Query Performance

A query that takes 2 milliseconds on a laptop can take 20 seconds on a production table with millions of rows. This lesson shows how to see what the database is doing with EXPLAIN, and the habits that keep queries fast as data grows.

Reading a query plan

EXPLAIN QUERY PLAN
SELECT c.name, o.order_date
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
WHERE c.country = 'UK';

The plan shows how the database will run the query: which table it reads first, whether it scans or searches, and which indexes it uses. Look for SCAN on large tables in queries that should only touch a few rows.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_customers_country ON customers (country);

EXPLAIN QUERY PLAN
SELECT c.name, o.order_date
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
WHERE c.country = 'UK';

With indexes, both tables are searched rather than scanned. (On tables this tiny it makes no measurable difference; on large ones it can be thousands of times faster.)

EXPLAIN in other databases

Write index-friendly conditions

A condition the database can answer with an index is called sargable (search-argument-able). Keep the indexed column bare on one side of the comparison:

CREATE INDEX idx_orders_date ON orders (order_date);

EXPLAIN QUERY PLAN SELECT id FROM orders WHERE strftime('%Y', order_date) = '2026';
EXPLAIN QUERY PLAN SELECT id FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';

Fetch only what you need

• Select specific columns, not *. Less data travels, and covering indexes become possible.
• Filter in SQL, not in application code. Don't load 100,000 rows to display 20.
• Paginate, preferably with keyset pagination for deep pages (lesson 7).
• Aggregate in the database. SELECT COUNT(*) beats fetching rows and counting them.

The N+1 query problem

# ✗ 1 query for customers + 1 query per customer = N+1 queries
for customer in db.query("SELECT id, name FROM customers"):
    orders = db.query("SELECT * FROM orders WHERE customer_id = ?", (customer.id,))

-- ✓ One query does the same work
SELECT c.id, c.name, COUNT(o.id) AS orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id
LIMIT 3;

Code that runs a query inside a loop is one of the most common performance problems, especially with ORMs that load related data lazily. Each round trip to the database has a cost; a join or an IN (…) list replaces hundreds of tiny queries with one.

Other habits

• Prefer EXISTS to COUNT(*) > 0 when you only need to know whether a row exists.
• Use UNION ALL instead of UNION when duplicates are impossible or acceptable.
• Keep statistics fresh (ANALYZE) so the planner makes good choices.
• Batch writes in transactions: 10,000 inserts in one transaction are far faster than 10,000 separate commits.
• Measure with realistic data volumes. Most databases can log slow queries; start optimising there.

Try it yourself

Compare the plans of these two equivalent queries after creating the index.