Indexes
What an index actually does, and the patterns that quietly stop the planner from using one.
Syntax
CREATE INDEX index_name ON table_name (column1, column2, ...);
CREATE UNIQUE INDEX index_name ON table_name (column1, ...);| Parameter | Type | Notes |
|---|---|---|
| index_name* | identifier | A name for the index. Never referenced directly in queries — the planner picks whether to use it on its own. |
| table_name (columns)* | identifier, identifiers | The table and the column(s) to index, in order. Order matters for a multi-column index — see leftmost-prefix below. |
| UNIQUE | modifier | Also enforces that no two rows share the same value(s) in the indexed column(s) — a constraint, not just an optimization. |
What it does
Without an index, finding rows matching a condition means checking every row
— a scan. An index is a separate, sorted structure (a B-tree, in nearly
every engine) mapping column values to row locations, so the engine can jump
straight to matching rows instead — a search. EXPLAIN QUERY PLAN on the
next page shows exactly which one happened.
Before: a full scan
Loading editor…
SCAN scratch_orders_noidx — every row gets checked. Fine on a handful of
rows; on a large table, this is the query that shows up in the slow-query log.
After: an index turns it into a search
Loading editor…
Same table, same query, one difference: SEARCH ... USING INDEX idx_scratch_customer (customer_id=?) instead of a scan. That's the entire
value proposition of an index in one line of output.
Composite indexes and the leftmost-prefix rule
A multi-column index is only useful for filters that start from its leftmost column(s) — it can't be used to jump straight to a match on a later column alone.
Loading editor…
Filtering on both columns, leading with customer_id — the index is used
for both conditions at once.
Loading editor…
Same index, but filtering on order_date alone — the second column — and
it falls back to a full scan. The index (customer_id, order_date) is sorted
by customer_id first, so there's no way to jump to an order_date match
without knowing customer_id too. If both columns get filtered independently
in different queries, that's two indexes, not one.
A pattern that silently defeats an index
Loading editor…
The index on customer_id exists, but wrapping the column in an expression
(customer_id + 0, or LOWER(some_text_col), or a leading-wildcard
LIKE '%x') means the engine would have to evaluate that expression for
every row to know which ones match — there's no way to look that up in an
index built on the raw column. Back to a scan. Rewrite the condition so the
indexed column is compared bare, or build the index on the expression itself
(most engines support that: CREATE INDEX ... ON t(LOWER(col))).
Gotchas
- Indexes aren't free. Every
INSERT/UPDATE/DELETEthat touches an indexed column has to update every index on that column too. A table with ten indexes has a much slower write path than one with two — index only the columns actually filtered, joined, or sorted on. - The planner might ignore your index anyway, even without an expression
wrapping the column — if the table is small, or the value isn't selective
(e.g. a
statuscolumn with only three distinct values across a million rows), a scan can genuinely be cheaper than the overhead of a lookup. That's not a bug; verify withEXPLAIN, don't assume. - A
UNIQUEindex is also a constraint. Adding one to a column with existing duplicate values fails outright — clean up the duplicates first.
- Reading query plans — how to check whether an index actually got used, on any engine.
- WHERE — the filters an index needs to match to be useful.
- Type coercion — an implicit type mismatch on an indexed column is another common way to silently lose index usage.
Safety checklist
- verify with
EXPLAINbefore and after adding an index — don't assume it helped - index the columns actually used in
WHERE,JOIN ON, andORDER BY— not just "the ID" - for a multi-column index, order columns by how they're actually filtered, leftmost first
- weigh write-path cost before adding an index to a heavily-written table