Why not index every column?
Indexes are not free:
- Writes slow down. Every INSERT, UPDATE, or DELETE must also update every index on the table. Ten indexes means eleven structures to keep in sync per write.
- They use space. Each index is a sorted copy of its column(s) plus pointers.
So you index the columns your WHERE clauses and JOIN ON conditions actually use, and stop there. Primary keys are indexed automatically, which is one more reason joins on ids are fast.
And sometimes an index you DO have goes unused:
LIKE '%@example.com'(wildcard first) cannot use the index. The index is sorted by each value's leading characters, and this pattern says nothing about how the value starts, so there is no spot in the sorted order to jump to, and every entry must be checked anyway. (A phone book sorted by last name is equally useless for finding names ending in "-son".)LIKE 'ana%'(prefix) pins down the start, so the index works.- Wrapping the column in a function, like
WHERE lower(email) = '...', hides the sorted values, so the index is skipped. - On tiny tables the database may just scan anyway, correctly, because a scan of 10 rows is cheaper than tree hops.
Code exercise · sql
Run it. Same indexed column, two plans: the leading-wildcard LIKE falls back to SCAN, while equality gets SEARCH ... USING INDEX. (Plan wording varies by SQLite version, so no pass/fail check here.)
Quiz
Your app's slowest query is `SELECT * FROM orders WHERE customer_id = ? ORDER BY created_at DESC LIMIT 10;` on a 20-million-row orders table with no extra indexes. Best single fix?
Problem
This query runs constantly and is slow: SELECT name FROM products WHERE sku = 'AB-1234'; Which column should you index? Answer with just the column name.
Quiz
There is an index on users(email), yet `WHERE lower(email) = 'ana@x.com'` still does a full scan. Why?