Learn

An index is a separate sorted data structure that lets the DB find rows by a column's value in O(log n) instead of O(n).

Advanced has the deep dive. Here's the practical 80/20.

Why

SELECT * FROM users WHERE email = '[email protected]';

Without an index: full table scan. 1M rows = 1M comparisons. With an index on email: B-tree lookup. 1M rows = ~20 comparisons.

The difference shows up the moment your tables get real. A query that ran in 2ms with 1000 rows is suddenly 2 seconds at 1M rows.

Creating

CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_email ON users(email);    -- enforces uniqueness too
CREATE INDEX idx_users_country_age ON users(country, age);   -- composite

Name conventions: idx_<table>_<columns>. Doesn't matter to the DB, matters for humans reading EXPLAIN output.

When you need one

• A column you frequently filter by (WHERE)
• A column you frequently sort by (ORDER BY)
• A foreign key column (most engines DON'T auto-index FKs!)
• A column used in a JOIN

Rule of thumb: if a column appears in WHERE, ORDER BY, or JOIN ... ON more than rarely, index it.

When you don't

• Columns rarely queried
• Tiny tables (<1000 rows — full scan is faster than index lookup)
• Columns with low cardinality and no = predicate (active = TRUE on a 50/50 split table — index doesn't help)
• Hot-write tables where indexes slow inserts

EXPLAIN

The DB shows you whether it used an index:

EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = '[email protected]';

Look for SEARCH ... USING INDEX <name>. If you see SCAN ..., the query is reading every row.

Costs

• Disk space: an index is typically 30-40% the size of the column it indexes
• Slower INSERT/UPDATE/DELETE — every change updates every relevant index
• Optimizer has more options (usually fine, occasionally picks wrong)

Don't index every column "just in case". Each index costs writes, RAM (kept in cache), and disk.

Composite index column order

CRITICAL rule. An index on (A, B) is useful for:

• WHERE A = ? ✓
• WHERE A = ? AND B = ? ✓
• WHERE B = ? ✗ (cannot use the index)

Leading column must be in the WHERE, or the index is useless.

Design composite indexes to match your most common queries. If you have:

WHERE status = 'active' AND created_at > '2026-01-01' ORDER BY created_at

The right index is (status, created_at) — equality first, then the range/sort column.

Indexes can speed up sorts

SELECT * FROM users ORDER BY age;

If age has an index, the DB can walk the index in order — no sort step. Without one, it reads everything and sorts in memory (or on disk for big results).

In summary

Indexes are how SQL stays fast at scale. Add them where queries demand them; resist over-indexing. EXPLAIN is your truth source.

The Advanced course goes deep on B-trees, covering indexes, partial indexes, and EXPLAIN reading. This lesson is the practical foundation.