Indexing & Query Performance Basics

What an index actually buys you

Without an index, finding rows matching a WHERE condition requires a sequential scan: reading every row in the table and checking each one. An index (almost always a B-tree, conceptually a sorted, navigable structure — the internal implementation is deliberately out of scope for this roadmap, per its own stated boundaries) lets the engine jump directly to matching rows instead, the same way a book's index lets you find a topic without reading every page. The trade-off is real, not free: an index costs extra storage, and it costs extra write time, since every INSERT/UPDATE/DELETE on an indexed column has to update the index too — indexing every column "just in case" is a genuine anti-pattern, not a safe default.

When an index helps — and, just as importantly, when it doesn't

An index on a column helps a query that filters, joins, or sorts on that column — but only in some shapes of query:

WHERE email = 'a@example.com'      -- index on email: helps a lot
WHERE last_name LIKE 'Sm%'          -- index on last_name: helps (prefix match)
WHERE last_name LIKE '%son'         -- index on last_name: does NOT help (suffix match)
WHERE YEAR(created_at) = 2023       -- index on created_at: does NOT help (wrapped in a function)
WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01'  -- helps (equivalent, unwrapped)

Two patterns above are worth internalizing as a pair, because they express the exact same intent while one is index-friendly and the other isn't: wrapping an indexed column in a function (YEAR(created_at)) generally prevents the engine from using a standard index on that column at all, because the index is sorted by the column's raw value, not by the function's output — rewriting the condition to compare the raw column against a range (as the last line does) restores the index's usefulness for exactly the same logical filter. This single rewrite is one of the most common real-world "why is this query suddenly slow" fixes.

Similarly, a leading % in a LIKE pattern ('%son') defeats a standard B-tree index, because the index is sorted by the string's beginning, not its end — there's no way to binary-search toward "ends with son" the way you can toward "starts with Sm". A trailing % ('Sm%'), by contrast, is exactly a prefix search, which a B-tree index supports directly.

Composite indexes and column order

An index can span multiple columns (CREATE INDEX ON orders (customer_id, order_date)), and column order matters: this index efficiently supports filtering on customer_id alone, or on customer_id AND order_date together, but does not efficiently support filtering on order_date alone — the same way a phone book sorted by (last name, first name) doesn't help you find everyone named "John" without scanning the whole book, even though it trivially helps you find "Smith, John." A useful, interview-safe heuristic: put the column used for equality filters first, range filters or sort columns after.

Reading an EXPLAIN plan at a glance

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

You don't need to master every line of a query planner's output for a coding-interview-level question — recognizing the two or three highest-signal terms is enough:

Seq Scan / Full Table Scan — the engine read every row; expected on small tables or when no useful index exists, worth explicitly flagging as expensive on a table you know to be large.
Index Scan / Index Seek — the engine used an index to jump straight to matching rows.
Estimated rows / cost — the planner's own guess at how much work a step will take, used to compare alternative plans, not a guarantee of actual runtime.

Being able to say "I'd expect this to need a sequential scan unless there's an index on customer_id, in which case I'd expect an index scan" out loud is the practical bar this subtopic is aiming for — not memorizing every plan node type an engine can produce.

Why interviewers care

Indexing is the one theory topic that connects most directly back to query-writing itself: an interviewer who asks "would this query benefit from an index, and on which column(s)" after you've written a correct query is checking whether you think about queries as things that eventually run against real, large data — not just as string manipulation that returns the right rows on a five-row example table.

Pitfalls and interview gotchas

Assuming "add an index" is always the answer without identifying which specific column(s) and which specific query pattern would benefit.
Wrapping an indexed column in a function in a WHERE clause and being surprised the index isn't used — flag this as a known anti-pattern if it comes up.
Getting composite index column order backwards relative to how the table is actually queried.
Conflating a primary key with "the" index — a table can have (and often needs) several indexes beyond its primary key, on whichever columns are actually filtered/joined/sorted on in practice.

Where this goes next

This is the final subtopic in the roadmap's theory topic — from here, the strongest next step is going back through the coding subtopics (Joins, Aggregation, Subqueries & CTEs, Window Functions) and re-solving a handful of their problems from memory, without the theory open, which is the single best way to convert "I understood this while reading it" into "I can produce it under interview pressure."