Query Optimization Techniques

Once you can read a query plan, most performance problems fall into a few familiar patterns.

1. Select only what you need

SELECT * fetches every column — more data to read, transfer, and hold in memory, and it prevents covering indexes (an index containing every column a query needs, so the table itself isn't read). List the columns.

2. Keep conditions index-friendly ("sargable")

-- Can't use an index on ordered_on: the column is inside a function
SELECT id FROM orders WHERE STRFTIME('%Y', ordered_on) = '2025';

-- Can use it: the column is compared directly
SELECT id FROM orders WHERE ordered_on >= '2025-01-01' AND ordered_on < '2026-01-01';

Both return the same rows; only the second can use an index on ordered_on.

3. Filter early, aggregate late

Reduce rows as early as possible — in WHERE, before joins and grouping — so later steps process less data. Prefer WHERE over HAVING for conditions that don't involve aggregates.

4. Avoid N+1 queries

Running one query per row from application code (100 orders → 100 customer lookups) is often the biggest slowdown in real applications. Fetch related data with one join or one IN (...) query.

5. Paginate with keys, not big offsets

SELECT id, ordered_on FROM orders
WHERE id > 5          -- the last id from the previous page
ORDER BY id
LIMIT 2;

+----+------------+
| id | ordered_on |
+----+------------+
|  6 | 2025-04-02 |
|  7 | 2025-04-10 |
+----+------------+

OFFSET 100000 makes the database read and discard 100,000 rows; keyset pagination jumps straight to the right place using an index.

6. Watch for accidental row multiplication

Joining two one-to-many relationships at once multiplies rows:

SELECT c.name,
  COUNT(o.id)          AS orders_wrong,
  COUNT(DISTINCT o.id) AS orders_right
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE c.id = 1
GROUP BY c.name;

+--------------+--------------+--------------+
|     name     | orders_wrong | orders_right |
+--------------+--------------+--------------+
| Ada Lovelace |            4 |            3 |
+--------------+--------------+--------------+

Each order appears once per item, so counting orders after joining items over-counts. Aggregate each relationship separately (in CTEs or subqueries), then join the results.

7. Keep statistics fresh

The planner chooses plans from statistics about your data. After large imports or deletes, run ANALYZE (PostgreSQL, SQLite) or ANALYZE TABLE (MySQL) so it has accurate numbers.

8. Measure, don't guess

Always compare EXPLAIN ANALYZE timings before and after a change, on realistic data volumes. A query that's fast on 100 rows can behave very differently on 10 million.