Short answer
Skip indexes on very small tables, low‑cardinality columns that return most rows, or when frequent writes make maintenance costly.
Key points
- Index small tables only if scans are slower than index lookup.
- Low‑cardinality columns (e.g., status flags) often make indexes useless.
- Heavy write traffic can negate read performance benefits.
- Batch updates can still use indexes if read traffic justifies it.
- Monitor query patterns before adding indexes.
Indexes help only when they speed up reads enough to outweigh their maintenance cost. If a table is tiny, the optimizer will scan it faster than it can use an index. Low‑cardinality columns that return a large share of rows also make indexes a waste.
Small tables and low‑cardinality columns
For tables with fewer than a few hundred rows, a full table scan is usually cheaper than building and traversing an index. Columns that return 70% or more of the rows—common with boolean flags or a small set of categories—lead the optimizer to ignore the index and perform a scan instead.
Write‑heavy workloads
Indexes must be updated on every insert, update, or delete. If a table experiences frequent batch writes and reads are infrequent, the write cost may outweigh the read benefit. In such cases, consider keeping the table unindexed or adding indexes only for critical read paths.
Frequently asked questions
Can a poorly chosen index hurt performance?
Yes, an unnecessary index adds write overhead and can slow queries if the optimizer chooses a less efficient plan.
When is it still worth indexing a low‑cardinality column?
If the column is used in a join or as part of a composite key where the index can narrow the result set significantly, it may still be beneficial.
How often should I reassess my indexes?
Reevaluate after major schema changes, significant growth in data volume, or a noticeable change in query patterns.
Comments
No comments yet. Be the first to react.