When Indexes Help, and When They Don't

The cost of an index

Indexes are not free, which is why databases do not simply index everything by default:

• Writes slow down. Every INSERT, UPDATE, or DELETE has to update every index on the table as well. Ten indexes means eleven structures to keep in sync on each write.
• They use space. Each index is a sorted copy of its columns plus pointers back to the rows.

So the rule is to 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.

Sometimes an index you already have goes unused:

• LIKE '%@example.com', with the wildcard first, cannot use the index. The index is sorted by each value's leading characters, and this pattern says nothing about how a value starts, so there is no spot in the sorted order to jump to and every entry has to be checked anyway. A phone book sorted by last name is equally useless for finding names ending in "-son". A prefix pattern like LIKE 'ana%' pins down the start, so the index works.
• Wrapping the column in a function, as in WHERE lower(email) = '...', hides the sorted values from the planner and the index is skipped.
• On tiny tables the database may scan anyway, and it is right to, because reading 10 rows costs less than walking a tree.

Two patterns on the same indexed column

The leading-wildcard LIKE falls back to a scan while the equality test gets a search, even though both query the same indexed column. Plan wording varies by SQLite version, so there is no pass or fail check here.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT,
  name TEXT
);

CREATE INDEX idx_users_email ON users(email);

-- Wildcard at the START: the sorted index cannot help
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email LIKE '%@example.com';

-- Exact match: index does the work
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'ben@example.com';

The index exists in both cases and is perfectly healthy. What decides the plan is whether the condition gives the planner a place to start looking in sorted order.

That is worth remembering when a query is slow despite having "an index on that column". The question is never whether an index exists, it is whether the query is written in a way the index can serve.

Indexing the column a slow query filters on

For SELECT * FROM orders WHERE customer_id = ? ORDER BY created_at DESC LIMIT 10; on a 20-million-row table with no extra indexes, the best single fix is:

CREATE INDEX idx_orders_customer ON orders(customer_id);

The WHERE clause filters by customer_id, so that index turns a 20-million-row scan into a targeted search over one customer's orders, which is likely a few dozen rows.

Indexing every column instead would slow down all writes and buy nothing extra for this query, since only customer_id appears in the filter.

The expert answer is a combined index on (customer_id, created_at), which is the same idea taken one step further: the index then delivers the rows already sorted, so the ORDER BY and LIMIT 10 become nearly free instead of sorting the whole matching set.

Choosing the column from the WHERE clause

For the constantly-running query SELECT name FROM products WHERE sku = 'AB-1234';, the column to index is sku.

CREATE INDEX idx_products_sku ON products(sku);

The WHERE clause tests sku for equality, which is the best case an index can be given: a single exact value to locate in sorted order.

The name column is merely displayed, and displayed columns gain nothing from an index. The database has to read the row anyway once the index has told it which row to read, so indexing name here would add write cost for no read benefit at all.

A sku is also a natural candidate for UNIQUE, which creates an index as a side effect while additionally preventing two products from claiming the same code.

A function on the column defeats the index

With an index on users(email), the query WHERE lower(email) = 'ana@x.com' still scans the whole table.

The index is a sorted list of the values as they are stored. Wrapping the column in a function asks about different values, the lowercased ones, and the index holds no sorted arrangement of those, so there is nowhere for the planner to jump to. It has to compute lower(email) for every row and compare.

Two fixes work:

The first is usually better for email, where case genuinely carries no meaning and normalizing on the way in also stops two accounts differing only in capitalization. The same trap catches WHERE date(created_at) = ... and WHERE price * 100 > ..., so it is worth spotting the shape rather than memorizing the example.