Indexes and query plans

An index is a sorted copy of some columns, plus a pointer back to the row. That sentence explains almost everything an index can and cannot do: it can find a value fast, it can return rows already in order, and it costs time on every write because the copy has to be maintained.

Both engines use B-trees by default.

The leftmost prefix rule

A composite index is sorted by the first column, then the second within equal firsts, and so on. Like a phone book sorted by surname then first name.

CREATE INDEX ix_order_customer_placed ON "order" (customer_id, placed_at);

This is why column order in a composite index is a decision, not a formality. The rule of thumb: equality columns first, then the one you range-scan or sort by.

A consequence people miss: (a, b) makes a separate index on (a) redundant, but not one on (b). Two indexes (a, b) and (a) are usually one index too many.

InnoDB is clustered, PostgreSQL is not

This is the biggest structural difference between the two, and it changes design decisions.

So in MySQL: keep the primary key small, and prefer sequential keys. A CHAR(36) UUID primary key is copied into every secondary index and scatters inserts across the whole file; a BIGINT AUTO_INCREMENT appends. In PostgreSQL the same choice costs much less.

PostgreSQL’s compensation is the index-only scan: if every column the query needs is in the index, it never touches the table at all.

-- Covered by ix_order_customer_placed: no heap access needed
SELECT customer_id, placed_at FROM "order" WHERE customer_id = 7;

-- PostgreSQL can also carry extra columns without making them part of the key
CREATE INDEX ix_order_cover ON "order" (customer_id) INCLUDE (status, placed_at);

MySQL gets the same effect by listing the columns in the index; it has no INCLUDE.

What stops an index from being used

A predicate is sargable when the engine can turn it into a range on indexed values. Wrap the column in a function and it cannot.

-- Not sargable: the index holds placed_at, not YEAR(placed_at)
WHERE YEAR(placed_at) = 2026
WHERE DATE(placed_at) = '2026-01-01'
WHERE lower(email) = 'a@b.com'

-- Sargable: same meaning, index-friendly
WHERE placed_at >= '2026-01-01' AND placed_at < '2027-01-01'
WHERE email = 'a@b.com'         -- with a case-insensitive collation, or:

When the function is genuinely needed, index the expression:

-- PostgreSQL: expression index
CREATE INDEX ix_customer_lower_email ON customer (lower(email));

-- MySQL: functional index (8.0.13+), or a generated column plus an index
CREATE INDEX ix_customer_lower_email ON customer ((lower(email)));

Other things that defeat an index:

• LIKE '%abc' — a leading wildcard has no prefix to seek to. LIKE 'abc%' is fine.
• OR across different columns — sometimes handled by a bitmap or index merge, often better rewritten as UNION ALL.
• A type mismatch. Comparing a varchar column to a number makes MySQL coerce the column, not the literal, and the index goes unused. This one is invisible in the query text.
• Low selectivity. An index on a boolean matching 60% of the table is more work than a scan, and the planner is right to ignore it.

Partial indexes, and the MySQL substitute

PostgreSQL can index a subset of rows, which is both smaller and more selective:

-- Only unshipped orders — the ones the dispatch queue asks about
CREATE INDEX ix_order_unshipped ON "order" (placed_at) WHERE shipped_at IS NULL;

-- And the uniqueness rule from lesson 2 that a plain UNIQUE cannot express
CREATE UNIQUE INDEX ux_one_open_coupon ON "order" (customer_id) WHERE coupon_code IS NULL;

MySQL has no partial indexes. The workaround is a generated column that is NULL for rows you want excluded — because UNIQUE ignores NULLs — plus an index on it.

Reading EXPLAIN

-- PostgreSQL: estimate only
EXPLAIN SELECT ...;
-- PostgreSQL: actually run it, and report real timings and I/O
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

-- MySQL: estimate
EXPLAIN SELECT ...;
EXPLAIN FORMAT=JSON SELECT ...;
-- MySQL 8.0.18+: actually run it
EXPLAIN ANALYZE SELECT ...;

EXPLAIN ANALYZE executes the statement. On a SELECT that is fine; on an UPDATE or DELETE it does the work. In PostgreSQL, wrap it in a transaction and roll back.

What to look for, in order:

1. Estimated rows versus actual rows. In PostgreSQL, rows=1000 next to actual rows=250000 means the statistics are wrong, and every decision above that node was made on a bad number. Run ANALYZE tablename (PostgreSQL) or ANALYZE TABLE tablename (MySQL).
2. The access method. Seq Scan / ALL on a large table with a selective WHERE is the usual finding. Index Scan, Index Only Scan, ref, range, eq_ref are index uses; const and eq_ref are the best cases.
3. A sort you did not want. Sort in PostgreSQL or Using filesort in MySQL means the order had to be produced. An index matching the ORDER BY removes it — and with LIMIT, that is often the whole difference.
4. Where the time actually went. In PostgreSQL, actual time is cumulative and per-loop: a node showing 0.5ms with loops=20000 cost ten seconds. Read the loop count before concluding a node is cheap.

Vocabulary map:

The cost of indexes

Every index is maintained on every INSERT, UPDATE of an indexed column, and DELETE. Ten indexes on a hot table make writes several times more expensive, and they consume cache that the table itself wanted.

Both engines expose usage, so this is measurable rather than a matter of taste:

-- PostgreSQL: indexes nothing has read
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY relname;

-- MySQL: same question
SELECT object_name, index_name, count_star
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL AND count_star = 0;

An index with zero scans since the last restart is pure write cost. Check the uptime before deleting one — a monthly report’s index legitimately shows zero on day two.

What to take away

• Composite indexes work left to right. Equality columns first, range or sort last.
• InnoDB stores the table in PK order and copies the PK into every secondary index: keep it small and sequential.
• A function around a column disables the index. Index the expression instead.
• LIKE 'abc%' uses an index; LIKE '%abc' cannot.
• PostgreSQL has partial and expression indexes; MySQL substitutes generated columns.
• In EXPLAIN, compare estimated to actual rows first, and read the loop count before believing a node is cheap.