Query Shape First, Indexes Second

The query is the lever

Before adding an index, ask: is the query asking the database to do too much? SELECT * when only three columns are needed, joins on tables you don't actually use, ORDER BY before LIMIT when the order is irrelevant — all of these are query-shape problems no index can fix.

The classic shape problems

Selecting columns you don't use (extra IO, defeats covering indexes).
Joining tables you don't filter or select from.
Aggregating before joining when you could aggregate after.
Computing expensive expressions in the SELECT list when they could be cached.
Pulling 10,000 rows back to the application to "find the right one" — let the database do the filtering.

The right order of optimisation

Fix the query shape — narrow the columns, narrow the rows, push work into SQL.
Then add the index — if EXPLAIN still shows a problem.
Then add the cache — only if the data is read often and changes rarely.
Then add the materialised view — only if the aggregation is genuinely expensive.

Code

Slim the SELECT, slim the JOIN·sql

-- Before: pulls every column from three tables, then filters in app
SELECT u.*, o.*, p.*
FROM   users u
LEFT JOIN orders o ON o.user_id = u.id
LEFT JOIN products p ON p.id = o.product_id;

-- After: only the columns and rows you need
SELECT u.id, u.name, o.id AS order_id, o.total
FROM   users u
JOIN   orders o ON o.user_id = u.id
WHERE  o.placed_at >= now() - INTERVAL '30 days';

Push aggregation into SQL·sql

-- Before: pull 100k rows, aggregate in Python
rows = db.execute("SELECT total FROM orders WHERE customer_id = %s", (cid,)).fetchall()
total = sum(r[0] for r in rows)

-- After: one query returns one number
total = db.execute(
    "SELECT SUM(total) FROM orders WHERE customer_id = %s", (cid,)
).fetchone()[0]

Query Shape First, Indexes Second

The query is the lever

The classic shape problems

The right order of optimisation

Code

External links

Exercise

Progress

Comments 0