🧠 Introduction

One of the most confusing problems for beginners is:

“I already created an index…
Why is MySQL still not using it?”

You check your table.
You confirm your index exists.
Yet, when you run the query with EXPLAIN, MySQL says:

type: ALL

Meaning: full table scan.
Not using your index.
Ignoring your hard work.

Don’t worry. This is extremely common — and very fixable.
Let’s walk through why MySQL ignores indexes and how to fix each cause, with simple explanations that even total beginners will love.

---

🔍 Step 1: Check If Index Really Exists

First things first.

Run:

SHOW INDEX FROM users;

Make sure your index is truly there.

Why this matters:

You’d be surprised how often developers think they created an index, but didn’t.

---

❌ Problem #1: You Used a Function in the WHERE Clause

Example:

SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

MySQL cannot use an index if you wrap the column in:

• LOWER()

• UPPER()

• DATE()

• YEAR()

• CAST()

• TRIM()

• etc.

✔️ Fix:

Transform the value, not the column.

SELECT * FROM users WHERE email = 'john@example.com';

---

❌ Problem #2: Data Type Mismatch

Example:

SELECT * FROM users WHERE id = '10';

If id is INT but you compare with a string → index may be skipped.

✔️ Fix:

SELECT * FROM users WHERE id = 10;

Always match data types.

---

❌ Problem #3: Index Field Not in the Leftmost Position

For composite indexes, MySQL only uses the “leftmost prefix”.

Example index:

CREATE INDEX idx_name_email ON users(name, email);

This index can optimize:

• name

• name + email

But NOT email alone.

❌ Slow:

WHERE email = 'abc@example.com'

✔️ Fix:

Add a dedicated index:

CREATE INDEX idx_email ON users(email);

---

❌ Problem #4: Low Selectivity (Too Many Rows Have Same Value)

If your column has values like:

• status = 'active' on 98% of rows

• type = 'user' on 90% of rows

MySQL knows the index is pointless.

✔️ Fix:

Index more specific fields
OR use composite index:

CREATE INDEX idx_status_created 
ON users(status, created_at);

Now the index becomes selective.

---

❌ Problem #5: Leading Wildcard Makes Index Useless

Beginners often write:

SELECT * FROM users WHERE name LIKE '%john';

If it starts with %, MySQL cannot use index.

✔️ Fix:

Use this:

WHERE name LIKE 'john%'

Or use full-text search:

ALTER TABLE users ADD FULLTEXT(name);

---

❌ Problem #6: OR Condition Without Proper Index

Example:

WHERE email = 'test@gmail.com' OR phone = '12345'

If both columns are not indexed → slow.

✔️ Fix:

Add indexes for both:

CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_phone ON users(phone);

---

❌ Problem #7: Sorting (ORDER BY) on Unindexed Column

Example:

SELECT * FROM logs ORDER BY created_at DESC;

If created_at has no index → full table scan + slow sort.

✔️ Fix:

CREATE INDEX idx_logs_created ON logs(created_at);

---

❌ Problem #8: Table Is Too Small — Index Not Used By Design

If your table only has:

• 20 rows

• 50 rows

• even 100 rows

MySQL may prefer a full scan.
This is normal.

✔️ Fix:

Don’t worry — this is not an error.

---

❌ Problem #9: Using Functions in JOIN

Example:

JOIN orders ON DATE(orders.created_at) = users.signup_date

This destroys index usage.

✔️ Fix:

Compare raw values:

JOIN orders ON orders.created_at BETWEEN users.signup_date
                                      AND users.signup_date + INTERVAL 1 DAY

---

❌ Problem #10: Statistics Need Updating

Sometimes MySQL simply guesses wrong.

Run:

ANALYZE TABLE users;

This refreshes table statistics so MySQL can choose a better execution plan.

---

🧪 Bonus: Use EXPLAIN to Check Index Usage

Example:

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

You want to see:

type: ref OR const
key: idx_email

You don’t want:

type: ALL

Which means “full table scan”.

---

🎉 Conclusion

When MySQL ignores your index, it's not being stubborn — it’s following strict rules.
Most of the time, the fixes are simple:

• Don’t wrap columns in functions

• Use the right data types

• Avoid leading %

• Use composite indexes correctly

• Keep table statistics fresh

Once you understand why the index is skipped, optimizing becomes easy and logical.