Missing Index scenario

Hook: A missing index is like a library with no shelf labels: every search has to walk the whole room.

Question: What is a missing index scenario in SQL, and how do you handle it?

Answer: It is when a query filters, joins, or sorts on columns that do not have a useful index, so the database scans far more rows than necessary. That usually increases CPU, memory use, and disk I/O, which makes the query slow under load. The right fix is not simply “add an index”; you first confirm the access pattern, then choose the smallest useful index that matches the query shape.

Interview-Ready Answer: In a missing index scenario, my query is paying for a scan because the columns in WHERE, JOIN, or ORDER BY are not supported by a useful index. I would verify that with EXPLAIN or, in SQL Server, the missing-index recommendation, then create the smallest index that matches the real workload, often a composite or covering index. I would also check the write cost, because every extra index makes inserts, updates, and deletes slower.

What a missing index really means

A missing index scenario is not magic; it is a mismatch between the query and the storage access path. The database wants to find rows with the least work. If it cannot jump directly to the needed rows, it reads a lot of pages, filters them one by one, and maybe sorts the result afterward.

How it works under the hood

1. The SQL engine parses the query and asks the optimizer, which is the component that picks the cheapest plan, to find a good path.
2. The optimizer checks available indexes and statistics, which are summary numbers about how data is distributed.
3. If a useful index exists, the engine can do an index seek, meaning it jumps to the matching key range instead of reading everything.
4. If no useful index exists, or if the index is too weak for the predicate, the engine uses an index scan or table scan, meaning it walks many or all rows.
5. If the query also sorts or joins on the same columns, the engine may add extra work like a sort or a hash join, which increases memory use.
6. In SQL Server, the plan may surface a missing-index hint or DMVs such as sys.dm_db_missing_index_details; in PostgreSQL/MySQL, you usually infer the same need from EXPLAIN, slow-query logs, and runtime metrics.
7. You validate the suggestion against the whole workload, because one slow query is not enough reason to add a permanent index.
8. You create the smallest useful index, then measure again to confirm that reads dropped and writes did not become too expensive.

Why the right index helps

Indexes are usually B-tree structures, which means they are sorted trees. That lets the engine find a value in roughly O(log n) time, then read nearby leaf pages efficiently. A scan is closer to O(n), because the engine must inspect many rows or pages. On a table with 10 million rows, that difference is huge: a good index may touch only a few pages, while a scan may touch thousands of pages and generate a lot of logical reads.

When to use it

• Queries that filter on selective columns, such as customer_id or email.
• Joins on foreign key columns, especially in large fact tables.
• ORDER BY queries where the sort column can be aligned with the filter columns.
• Top-N queries like recent orders, latest messages, or newest events.

When not to rush and add an index

Not every missing-index hint should become a real index. If a column has very low selectivity, like a boolean or a status value with only a few common states, the index may not help much because too many rows still match. Also, every extra index adds maintenance work on writes. On a busy OLTP system, a single table can have 3 to 8 useful indexes; adding 20 indexes often hurts more than it helps.

Important edge cases

• Wrong column order: In a composite index, the leading column matters. If the query filters by customer_id first, that column usually belongs first in the index.
• Functions on columns: A predicate like WHERE UPPER(name) = 'ALICE' may stop a normal index from being used unless you add an expression index or rewrite the query.
• Stale statistics: Bad stats can make the optimizer underestimate or overestimate row counts, which may hide a real missing index or make a bad one look useful.
• Parameter changes: A query that is fast for one parameter can be slow for another if the data is skewed.

Memory model: Seek is a shortcut; scan is a room search. If the shortcut exists, use it. If not, the engine has to walk the whole room.

Real-world story

Imagine a checkout service for an e-commerce app. Users open their order history page and expect the latest 20 orders for one customer to load instantly. The table has tens of millions of rows, but no index supports customer_id plus created_at. During a sale, the page starts timing out because every request scans a huge chunk of the orders table, then sorts the result.

What goes wrong: CPU climbs, disk reads spike, and the app server threads wait longer for the database. In SQL Server you might see rising logical reads and waits like PAGEIOLATCH or CXPACKET; in other systems you will still see the same shape: slow query logs fill up, p95 latency jumps, and customers report that order history never loads. The fix is usually a targeted composite index, not a random index on every column.

Follow-up & Tricky Questions:

• How do you choose the first column in a composite index? Start with the column most often used for equality filtering, then place range or sort columns after it. The leftmost part of the index is the part the optimizer can use most effectively.
• What is a covering index? It is an index that contains all the columns needed by the query, so the engine does not need to go back to the table for extra reads. That is useful when the query returns many rows and you want to avoid extra lookups.
• How do you decide whether the index actually helped? Compare before-and-after EXPLAIN output, logical reads, and latency under realistic data volume. A query that is faster in a toy dataset may not stay faster at production scale.
• Why might the optimizer ignore a new index? If the filter matches too many rows, or statistics say the index would not save work, a scan can still be cheaper. The optimizer chooses the plan it estimates as cheapest, not the plan you expected.
• What happens if the table is small? A scan may be perfectly fine because the cost of using the index is higher than reading the whole table. That is why index design is about workload, not just structure.
• Does a missing index suggestion always mean you should create one? No. Suggestions are workload-specific and can overlap with existing indexes. You should consolidate, test, and measure before changing production.
• Gotcha: will WHERE UPPER(name) = 'ALICE' use a normal index on name? Usually no, because the function changes the searched value. You either rewrite the predicate or create an expression index if your database supports it.
• Gotcha: does more indexing always speed up reads? No. Too many indexes slow down inserts, updates, and deletes, and they can bloat storage. The best index is the one that helps the common query pattern with the least maintenance cost.

Common Mistakes:

• Adding an index blindly: The correction is to inspect the real query, row counts, and selectivity first.
• Using the wrong column order: The correction is to match the leading index column to the most common equality filter.
• Ignoring write overhead: The correction is to remember that every extra index must be maintained on every write.
• Trusting one plan without testing: The correction is to validate with production-like data and repeated runs, because caches and stats can change the result.

Memory Hook: “Labels beat wandering.” An index is the shelf label that lets the database go straight to the right rows instead of wandering through the whole table.

Cheat Sheet:

• Missing index = query columns are not supported by a useful index.
• Symptoms: scans, high logical reads, high CPU, slow sorts, slow joins.
• Best fix: match the real WHERE / JOIN / ORDER BY pattern with a small index.
• Composite indexes often beat single-column indexes for real queries.
• Check selectivity, statistics, and write cost before creating anything.
• Measure before/after with EXPLAIN and real workload metrics.

Practice Tasks:

• Create a table with 100,000 rows, run a filter query without an index, then add the index and compare EXPLAIN.
• Try a query with WHERE plus ORDER BY, then design a composite index that supports both.
• Test a low-selectivity column like status and observe why it may not be the best candidate.