SQL · Chapter 35 of 42
SQL Indexes
An INDEX is a data structure (usually a B-tree) that speeds up lookups and joins on specific columns.
Trade-off: faster reads, slower writes (indexes must be updated on INSERT/UPDATE/DELETE).
When to index
Columns used in WHERE, JOIN or ORDER BY on large tables. Skip on small tables — a full scan is fine.
Composite indexes
`(a, b)` speeds queries filtering by `a` alone or `a` + `b`, but NOT by `b` alone.
Example 1 (sql)
CREATE INDEX idx_users_email ON users(email);Output
Index createdSpeed up email lookups.
Example 2 (sql)
CREATE UNIQUE INDEX idx_orders_num
ON orders(order_number);Output
Unique index (also enforces uniqueness)Doubles as a UNIQUE constraint.
Key points
- Speeds reads, slows writes.
- Index columns used in filters/joins.
- Composite indexes have column-order rules.
- Unique indexes enforce uniqueness.
💡 Note: Use your database's `EXPLAIN` to see whether an index is actually used for a query.