Every index is a second copy of part of the table that must be kept in sync. It costs storage and slows down writes. And the optimizer only uses an index when it is cheaper than reading the table. So indexing every column makes writes slow and rarely makes reads faster.
The cost of each extra index
An insert into a table with 8 indexes writes the row plus 8 index entries. An update on an indexed column updates that index too. On a table loaded with millions of rows a day, this adds up in time, disk space and memory pressure on the cache. This is called write amplification.
Why the database might ignore your index
- Low selectivity. An index on
statuswith 3 values, where 40 percent of rows are'PAID', is not worth using forWHERE status = 'PAID'. Jumping to 40 percent of the rows one at a time is slower than a sequential scan. The planner knows this and scans. - Composite index order. An index on
(customer_id, order_date)helps queries oncustomer_id, or on both. A query filtering only onorder_datecannot use it effectively, because the index is sorted by customer first. This is the leading column rule. - Implicit casts. Comparing a VARCHAR column with a number, such as
WHERE phone = 98765, makes the engine cast the column in every row and skip the index. - Functions on the column, such as
WHERE LOWER(email) = .... - Leading wildcard:
LIKE '%abc'cannot use a normal B-tree.LIKE 'abc%'can. - OR conditions across different columns often prevent a single index lookup, though some engines combine indexes.
- Stale statistics, so the planner thinks the table is tiny.
How to decide what to index
Start from the slow, frequent queries. Index the columns used in selective filters and join keys. Put the most selective, equality-filtered column first in a composite index. Drop indexes that your plans never use. Look at EXPLAIN to confirm, instead of assuming.
In bulk-load jobs, a common trick is to drop or disable indexes, load, and rebuild afterwards, because maintaining them row by row is slow.