Database Indexing
A data structure technique used to quickly locate and access data in a database without having to search every row.
Detailed Explanation
Think of an index like a book's index. Instead of reading every page to find a topic, you look it up in the back and go straight to the page. Indexes (usually B-Trees) drastically speed up SELECT queries but slow down INSERTs and UPDATEs because the index must be updated too. Proper indexing is the single most important factor in database performance.
Quick Summary
An index is a separate data structure that lets the database find rows by a column's value without scanning the table. It's the difference between a slow query and an instant one, and the most common DB performance lever.
Key Takeaways
- Default is a B-tree, good for equality and range queries; other types: hash, GIN/GiST (full-text, JSON, arrays), BRIN (huge sequential data).
- Composite indexes are ordered: `(a, b)` helps `WHERE a = ? AND b = ?` and `WHERE a = ?`, but NOT `WHERE b = ?` alone.
- Indexes speed reads, slow writes, each INSERT/UPDATE has to maintain every index on the table.
- Use `EXPLAIN ANALYZE` to confirm an index is actually being used; the planner sometimes ignores them.
- Partial and filtered indexes (`WHERE active = true`) shrink the index dramatically when you only query a subset.
When to use it
- Foreign keys, almost always worth indexing, especially for cascade and join performance.
- Columns used in WHERE, JOIN, and ORDER BY clauses of hot queries.
- Multi-column indexes for queries that filter on multiple fields together.
- Full-text search columns with GIN indexes in PostgreSQL.
Common Mistakes
- Indexing every column "just in case", bloats storage and slows writes.
- Not indexing foreign keys, causing slow joins and surprising lock escalation.
- Indexing low-cardinality columns (boolean, status) alone, the planner often prefers a sequential scan anyway.
- Forgetting to rebuild or analyze indexes after large data changes; the optimizer relies on accurate statistics.
Database Indexing, Frequently Asked
How do I know if I need a new index?
Profile your slow queries with `EXPLAIN ANALYZE` and check for sequential scans on large tables. Tools like pg_stat_statements (PostgreSQL) and Performance Schema (MySQL) surface the queries worth optimizing. Don't index speculatively, index by evidence.
Can I have too many indexes?
Yes. Each index costs storage and slows every write touching its column. A common heuristic: under 10 indexes per OLTP table; investigate any that aren't being used (Postgres exposes this via `pg_stat_user_indexes`).