Indexing Strategy

The MySQL and PostgreSQL sections of this track cover the mechanics of indexes in detail — how EXPLAIN reads a query plan, B-tree vs. GIN/GiST. This page is about the design-level decisions: what to index in the first place, and when to stop.

What to index#

Foreign keys. Any column used to join tables should almost always be indexed. Consider orders.user_id referencing users.id — without an index on user_id, every query joining orders back to their user (or filtering "this user's orders") scans the entire orders table. Some databases (MySQL's InnoDB) index foreign key columns automatically when the constraint is created; others (PostgreSQL) do not — always verify explicitly rather than assuming.

CREATE INDEX idx_orders_user_id ON orders (user_id);

Columns frequently filtered in WHERE. If an application constantly runs WHERE status = 'pending' or WHERE email = ?, those columns are direct index candidates — this is the single most common source of "just add an index" performance wins, exactly the pattern shown with EXPLAIN in the MySQL indexing page.

Columns frequently sorted (ORDER BY) or used in JOIN conditions. An index that matches a query's ORDER BY clause can let the database return already-sorted results directly from the index, avoiding an expensive separate sort step entirely.

Composite index column order#

Covered mechanically in the MySQL and MongoDB pages in this track (the "leftmost prefix rule"), but the design question is which column goes first. Two practical heuristics:

1. Put the most selective, most commonly-filtered-alone column first. A composite index on (user_id, status) serves both "this user's orders" and "this user's orders with this status" — but not "orders with this status" alone. If both access patterns matter and neither is a strict subset of the other, either order two separate indexes, or pick the order that matches the more common query.
2. Equality columns before range columns. An index on (status, created_at) serves WHERE status = 'shipped' AND created_at > '2026-01-01' efficiently — MySQL can use the equality match on status to narrow down to a contiguous range, then use created_at's ordering within that range. Reversing the order (created_at, status) loses that benefit, since the range condition on the first column prevents the second column from being used to narrow the search further.

-- Good: equality column first
CREATE INDEX idx_orders_status_created ON orders (status, created_at);

The cost of over-indexing#

Every index is a trade-off, not a free performance upgrade:

• Slower writes. Every INSERT, UPDATE, or DELETE touching an indexed column must also update that index's own data structure. A table with ten rarely-used indexes pays that write cost on every single insert, for read benefits that may never materialize.
• Storage. Each index is its own physical structure on disk, sized roughly proportional to the indexed columns' data plus the row pointers — a heavily-indexed large table can have its index storage rival or exceed the table's own data size.
• Diminishing, and sometimes negative, returns. An index on a low-cardinality column (few distinct values, like a boolean flag) rarely helps much on its own, since a large fraction of rows match any given value — the database may reasonably choose a full scan over the index anyway, and the index still costs on every write regardless of whether it's ever used.

The practical discipline: add an index when a real, observed query pattern needs it (backed by EXPLAIN, not guesswork), and periodically review which indexes are actually being used (sys.schema_unused_indexes in MySQL, pg_stat_user_indexes in PostgreSQL) — an index nobody's queries ever touch is pure cost with zero benefit, and safe to drop.

Common mistakes#

• Indexing every column "just in case" instead of indexing based on actual, known query patterns.
• Forgetting that PostgreSQL, unlike MySQL's InnoDB, does not automatically index foreign key columns — an unindexed foreign key is a very common, very avoidable source of slow joins in Postgres schemas.
• Choosing composite index column order arbitrarily instead of matching it to the dominant query pattern (equality-then-range, and matching the most common filter combination).
• Never revisiting indexes after launch — as query patterns shift over a system's life, indexes that mattered at launch can become dead weight, and new query patterns can go unindexed for months before anyone notices the slow query.