Detailed explanation
An index may not be used because the database cannot turn a predicate into an efficient index lookup, or because the optimizer estimates that a table scan will cost less. These are different situations: an index can be eligible but still not be chosen. Most optimizers compare estimated I/O, CPU work, and row counts for possible execution plans. Interviewers expect you to explain both index usability and cost-based selection, rather than assume that every indexed column guarantees an index seek.
A common usability problem is a non-sargable predicate: one that cannot directly narrow the search through an ordinary index. For example, WHERE YEAR(order_date) = 2026 typically prevents a direct range lookup on a normal index over order_date because the function is applied to the stored value. Rewrite it as WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01'. This half-open interval also includes every timestamp within 2026 without guessing the final day's time precision. Some engines can transform certain expressions, and expression indexes or indexed computed columns may support the original predicate. Similarly, LIKE '%phone' generally cannot use an ordinary B-tree for a selective prefix lookup, whereas LIKE 'phone%' may, depending on database and collation rules.
Implicit type conversions can also interfere with index access, especially when the database converts the indexed column rather than the parameter or literal. Composite indexes introduce another constraint: efficient lookup usually depends on predicates matching the leading index columns, although engine-specific alternatives exist. Even with usable predicates, a query returning a large fraction of rows may favour a scan, particularly if an index lookup requires many additional table-row fetches. Small tables can be cheaper to scan too.
Statistics guide these decisions by describing data distribution and estimated predicate selectivity. Stale or insufficient statistics can produce inaccurate estimates and an unsuitable plan. Diagnose the issue using the execution plan, comparing estimated and actual row counts where available, and checking predicates, parameter types, index definitions, and statistics. Distinguish an index scan from an index seek: scanning an index still uses it, but may not provide the selective access you expected.
Key points to remember
- Separate predicates that cannot support efficient index access from usable indexes rejected on cost.
- Functions, leading wildcards, and column-side conversions can prevent ordinary B-tree lookups.
- Use half-open date ranges instead of extracting the year from an indexed date column.
- Low selectivity, small tables, and expensive row lookups can make scans cheaper.
- Check execution plans, composite-index column order, and statistics before changing the query.
Common mistakes candidates make
- Claiming that functions or leading wildcards always prevent every kind of index usage.
- Assuming an index seek is always faster than a scan, regardless of the number of matching rows.
- Forcing an index before checking execution plans, statistics, and parameter data types.
Likely follow-up questions
Why is the half-open date range better than BETWEEN?
BETWEEN includes both endpoints, so using the next year's start would include an unwanted boundary value. An exclusive upper bound also avoids missing timestamps later on 31 December.
Can an index support a function applied to a column?
Yes, some databases support expression indexes or indexes on computed columns. The query expression must match what the optimizer recognises, and the expression must satisfy the database's indexing restrictions.
How would you investigate an unexpected table scan?
Inspect the execution plan and compare estimated versus actual row counts using the engine's runtime-plan tooling where safe. Check predicate structure, parameter types, index column order, statistics freshness, and how many rows the query actually returns.
Interview tip: Start with the usability-versus-cost distinction, demonstrate the date-range rewrite, and finish by explaining how you would verify the execution plan.