HardExperience level: 8-15 years1 min read

An index exists but the query still does a full scan — what are the likely reasons?

Reviewed by Gurusankar M.

Asked inAmazonMicrosoftOracle-shopsGoogle
#index#full scan#sargable#selectivity#implicit cast#statistics
Report issue

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.

On this page

Concept Made Simple

Coffee Chat Question

“An index exists but the query still does a full scan — what are the likely reasons?”

Remember It Faster

Mind Map Answer

Function on column
WHERE UPPER(x)=? → not SARGable
Implicit cast
col = '123' on a numeric col
Low selectivity
scan cheaper than many seeks
Stale stats
bad row estimates

Learn by Doing

Hands-on Keyboard

sql
-- 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';

Think Beyond the Expected

What If?

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:

  • SARGable predicates
  • function/expression on column
  • implicit cast
  • selectivity
  • stale stats
  • leftmost prefix

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

  1. What does SARGable mean?
  2. How do function-based indexes help when you must use a function?
  3. How does selectivity drive the optimizer's choice?

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.
Open inChatGPTGeminiClaude

Was this answer helpful?

Affiliate links

Tools for Your Developer Workspace

Equipment for a comfortable, productive setup.

As an Amazon Associate I earn from qualifying purchases.