I have interviewed quite a few people, and whenever I ask, "How do you filter large data efficiently?"
almost everyone says, "add index!!!" That's it!! It solves our problem, sir!
but when I dig a bit deeper… they don’t realize their own query is not even using that index.
Everyone says indexes make things fast.
Reality is simpler:
* you already have indexes
* your query just made them useless
Here are 6 cases where your index is literally ignored.
1. You indexed it… then destroyed it yourself
WHERE DATE(created_at) = '2025-03-21'
You: "I added index on created_at"
DB: "Cool… I’ll ignore it"
You wrapped the column with the date function→ index order gone
Real fix:
WHERE created_at >= '2024-01-01'
AND created_at < '2024-01-02'
Index works on raw column, not your modified version.
2. You searched backwards… index gave up
WHERE email LIKE '%@gmail.com'
Index can’t even start
Why this hits:
Most people think LIKE always uses index
Better design (store domain separately):
WHERE email_domain = 'gmail.com'
Index is like Google search — it needs a starting word.
If any people knows better solution, please comment!
3. Your query works… but secretly scans everything
WHERE user_id = '123'
Column = INT, but you query as string
DB silently converts types
Index becomes useless
Why this is scary:
No error. No warning. Just slow.
Fix:
WHERE user_id = 123
4. Your “perfect index” fails because of column order
Index:
(user_id, created_at)
Query:
WHERE created_at = '2025-03-21'
Index exists. Still not used.
Why this hits:
People create index… but don’t understand how it’s stored
How Index stored:
user1 → dates
user2 → dates
user3 → dates
You’re searching only by date → no entry point. Needs to be left to right
5. One tiny ‘>’ breaks your whole index
Index:
(user_id, created_at, status)
Query:
WHERE user_id = 10
AND created_at > '2025-03-21'
AND status = 'active'
Index works… then suddenly stops
Example to feel it:
Index is stored like:
user_id = 10
→ 2025-03-01 → active
→ 2025-03-21 → inactive
→ 2025-03-22 → active
→ 2025-03-23 → pending
When you say:
created_at > '2025-03-21'
👉 DB jumps to:
2025-03-21 → ...
From here, data is no longer neatly grouped by status
So:
* It cannot efficiently use status = 'active' from the index
* It has to scan those rows and filter manually
Best solutions (what strong candidates say):
Option 1: Reorder index based on filter priority
(user_id, status, created_at)
6. You think you optimized… you actually forced full scan
SELECT *
FROM orders
WHERE amount + 10 > 100;
Index on amount = useless
Because you changed the column:
amount + 10
Fix:
WHERE amount > 90
Index only works when column is untouched.
One line that changes everything!!!
Most people think:
"Do I have an index?"
Strong candidates think:
"Is my query written in a way that allows index usage?"
Be the kind of SQL candidate who doesn’t just add indexes…
but actually understands when they work — and when they don’t.
