Indexes make reads fast, but they aren't free. Every index you add makes every write to the table slower and takes up disk space. Good indexing means adding the few indexes your real queries need and resisting the rest. This lesson covers the costs, the cases where an index doesn't help, and a checklist for deciding.
The deal you make#
Every index is a trade: you speed up some reads and pay for it everywhere else.
| You get | You pay |
|---|---|
| Faster reads on the indexed columns | Slower INSERT, UPDATE and DELETE on the whole table |
| Extra storage | |
| Ongoing maintenance |
Why indexes slow writes#
Each index is its own B-tree (see Read Heavy Indexing). Every write must update the table and every index:
| Indexes | What one INSERT does |
Writes |
|---|---|---|
| 0 | Write the row | 1 |
| 1 | Write the row + update 1 B-tree | 2 |
| 5 | Write the row + update 5 B-trees | 6 |
| 15 | Write the row + update 15 B-trees | 16 |
Each index update means finding the position, inserting the key and maybe splitting a node.
Updates to an indexed column are often worse than inserts. Take this statement:
UPDATE users SET email = 'new@example.com' WHERE id = 5;If email is indexed |
If email is not indexed |
|---|---|
| 1. Remove the old email from the index B-tree | 1. Update the row. Done. |
| 2. Insert the new email into the index B-tree | |
| 3. Update the row |
Storage cost#
An index stores a copy of the indexed columns plus a pointer for every row. A handful of secondary indexes, especially composite and covering ones that copy several columns, can together take up as much space as the table itself. That's more disk, more memory needed to keep indexes cached, and longer backups.
When not to index#
An index only pays off when queries use it often and selectively. These five cases usually fail that test.
1. Small tables#
With a few hundred rows, a full scan already takes a fraction of a millisecond. An index saves almost nothing and still costs on every write. Tables under roughly a thousand rows rarely benefit.
2. Low-cardinality columns#
Cardinality is the number of distinct values in a column.
Take a status column with the values 'active', 'inactive' and 'pending' across 10 million rows. WHERE status = 'active' matches about a third of the table. Following millions of index pointers is slower than just reading the table, so the query planner ignores the index and does a full scan.
| Good cardinality (index helps) | Poor cardinality (index rarely helps) |
|---|---|
email: one row per value |
is_active: 2 values |
user_id: millions of values |
status: a handful of values |
phone_number: nearly unique |
country: ~200 values across millions of rows |
What matters is how selective a particular query is. If a rare value (say, the 0.1% of rows that are 'pending') is queried often, an index or a partial index on just those rows can still pay off.
3. Write-heavy, rarely read tables#
Picture a logging table with 100,000 inserts per second that is read once a week for analytics. Five indexes mean roughly 6× the write work, for a query that runs weekly.
A better plan is to write with no (or minimal) indexes, then either:
- build an index just before the analytics job and drop it afterwards, or
- export the data to a system built for analytics.
4. Columns you never query#
Say a users table has id, email, name, bio, avatar_url and created_at. Index only what real queries touch:
| Real query | Decision |
|---|---|
WHERE email = ? |
Index email ✓ |
WHERE created_at > ? |
Index created_at ✓ |
ORDER BY name |
Index name ✓ |
bio, avatar_url never appear in WHERE, JOIN or ORDER BY |
No index ✗ |
It sounds obvious, but adding indexes "just in case" is one of the most common performance mistakes.
5. Frequently updated columns#
Say last_login is updated on every visit, at 100,000 logins per hour. An index on it means 100,000 index updates per hour, each removing the old value and inserting the new one. Unless you often query "users who logged in between X and Y", this index costs more than it saves.
The over-indexing problem#
A common story: a table has 15 indexes and writes are slow. "But reads are fast!" Often only 3 of those indexes are used by real queries. The other 12 slow down every write and waste storage.
To find unused indexes:
- PostgreSQL:
pg_stat_user_indexesshows how often each index is scanned. - MySQL:
sys.schema_unused_indexes, orperformance_schemaindex statistics.
Then drop what isn't used.
Decision checklist#
Before adding an index, walk through these questions:
| # | Question | If… | Then |
|---|---|---|---|
| 1 | Does the column appear often in WHERE, JOIN or ORDER BY? |
No | Don't index |
| 2 | Are the queries on it selective (high cardinality)? | No | Probably don't |
| 3 | Is the table write-heavy? | Yes | Only the essential ones |
| 4 | Is the table tiny (under ~1,000 rows)? | Yes | Don't bother |
| 5 | Would it be a covering index for a critical hot query? | Yes | Likely worth the space |
The same rules, as scenarios:
| Scenario | Index? | Why |
|---|---|---|
Frequent WHERE on a high-cardinality column |
Yes | Fast, selective lookups |
Column used in JOIN or ORDER BY |
Yes | Avoids scans and sorts |
| Critical query that a covering index would serve | Yes | Skips the table lookup |
| Small table | No | A scan is fast enough |
| Low-cardinality column | Usually no | The planner scans anyway |
| Write-heavy, rarely read table | No | Write cost isn't worth it |
| Column never queried | No | Pure waste |
| Frequently updated column | Probably not | Constant index churn |
The same trade-off in DynamoDB: every global secondary index has its own write capacity, and each table write that touches indexed attributes also writes to those indexes. More indexes literally means a higher cost per write, so high-throughput tables keep their secondary indexes to a minimum.
Which databases use which structure#
Different engines pick different index structures to match the workload they target.
| Database | Storage engine | Main structure |
|---|---|---|
| PostgreSQL | Built-in | B+ tree indexes over a heap table |
| MySQL | InnoDB (default) | B+ tree, with the table clustered on the primary key |
| SQLite | Built-in | B+ tree |
| MongoDB | WiredTiger | B-tree |
| Cassandra | Built-in | LSM tree (SSTables) |
| RocksDB, LevelDB | Built-in | LSM tree |
| Redis | In-memory | Hash tables, skip lists (no on-disk tree) |
| Elasticsearch | Lucene | Inverted index |
The pattern:
- General-purpose SQL databases (PostgreSQL, MySQL, SQLite, Oracle, SQL Server) use B+ trees: balanced reads and writes, updates in place, and they fit well with transactions.
- Write-heavy stores (Cassandra, RocksDB, LevelDB, ScyllaDB) use LSM trees: much cheaper writes, somewhat slower reads. See Write Heavy Indexing.
- Search engines (Elasticsearch, Solr) use inverted indexes that map terms to documents.
- In-memory stores (Redis, Memcached) use hash tables and similar structures, since there is no disk layout to optimise. See Hash Indexes.
Key takeaways#
- Every index speeds up some reads and slows down every write, because each index must be updated.
- Index columns that appear in frequent, selective
WHERE,JOINorORDER BYclauses. - Skip indexes on tiny tables, low-cardinality columns, rarely read logging tables and columns you never query.
- Audit index usage regularly and drop indexes that aren't used.
- B+ trees suit balanced workloads, LSM trees suit write-heavy ones, inverted indexes suit search, and hash tables suit in-memory key-value access.