An index exists but the query still does a full scan — what are the likely reasons?
⚡ Short Answer
Common causes: a non-SARGable predicate (function/expression on the column, leading-wildcard LIKE), an implicit type cast (string vs number), low selectivity (the optimizer judges a scan cheaper), stale statistics, or the column isn't the index's leftmost prefix.
☕Coffee Chat Question
Concept Made Simple
“An index exists but the query still does a full scan — what are the likely reasons?”
🧠Mind Map Answer
Remember It Faster
⌨️Hands-on Keyboard
Learn by Doing
-- NOT SARGable (index unused)
WHERE YEAR(created_at) = 2026;
-- SARGable rewrite (index used)
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';🔥What If?
Think Beyond the Expected
WHERE phone = 9876543210 ignores the index on a VARCHAR phone column — why?
Type mismatch: comparing a VARCHAR column to a numeric literal forces an implicit cast on the column, making the predicate non-SARGable so the index can't seek. Quote the literal ('9876543210') to match the column type.
😂Real World
Wrapping a column in a function (YEAR(), UPPER()) or an accidental type mismatch silently disables an index — a top cause of 'we have an index but it's still slow' tickets.
🎯Interviewer's Expectation
Keywords they're listening for:
⚠️Common Mistakes
- ✗Wrapping indexed columns in functions
- ✗Type mismatches forcing casts
- ✗Assuming an index guarantees it'll be used
✅Best Practices
- ✓Keep predicates SARGable (no function on the column)
- ✓Match literal types to columns
- ✓Use function-based/expression indexes when needed; keep stats fresh
🔁Follow-up Questions
- 1What does SARGable mean?
- 2How do function-based indexes help when you must use a function?
- 3How does selectivity drive the optimizer's choice?
🧩Related Technologies
Continue Learning with AI
Take this question deeper with your favourite AI assistant. Pick a depth, copy the prompt, or open it directly — AI is your learning companion, not a shortcut.
Plain-language foundations
I'm preparing for a software engineering interview and want to understand this from scratch, as a beginner. Topic: Optimization (SQL) Interview question: "An index exists but the query still does a full scan — what are the likely reasons?" Please: 1. Explain the core idea in simple, plain language, using an everyday analogy. 2. Define any technical terms you use. 3. Walk through one small, concrete example. 4. Finish with a single sentence I can easily remember. Keep the tone friendly and assume I'm new to this topic.
Was this answer helpful?
⭐ Featured Products
Support our platform by exploring our recommended products.
As an Amazon affiliate, purchases through these links may earn us a small commission — at no extra cost to you. It helps keep Full Stack Interview Guru free.