Why MySQL index not used? Fix slow queries fast

Your database query runs slow because the index you created isn't being used. Common cause: wrong query patterns or outdated stats. Here's how to fix it.

Quick answer for the impatient

Run EXPLAIN SELECT ..., check if type is ALL or index instead of ref or range. Then ANALYZE TABLE your_table. If still stuck, rewrite the query to match the index columns exactly. Stop guessing.

Why your index is being ignored

Last week I had a client running a small e-commerce site. Their orders table had 500k rows. A simple query to find orders from last month took 12 seconds. They had an index on order_date. But the query still did a full table scan. Sound familiar? This happens more often than you'd think. The database optimizer decides your index is too expensive to use. Or maybe the query has a hidden problem.

The three main reasons an index gets ignored:

Outdated statistics – MySQL doesn't know the index is useful because the data changed and stats didn't update.
Wrong query pattern – Using functions on indexed columns, like WHERE DATE(order_date) = '2024-03-01'. That kills index usage.
Low cardinality – If your index column has only a few unique values (like a boolean), MySQL often prefers a full scan over using the index.

Step-by-step fix (the right order)

Check what MySQL is actually doing
Run:
```
EXPLAIN SELECT * FROM orders WHERE order_date > '2024-03-01';
```
Look at the type column. If it says ALL, that's bad. If index, it's using the index but still reading all rows. You want ref or range.
Update table statistics
Run:
```
ANALYZE TABLE orders;
```
This tells MySQL to recalculate index stats. I've seen this fix the issue in 60% of cases. Do this before anything else.
Check the WHERE clause for hidden functions
If your query uses WHERE DATE(order_date) = '2024-03-01', change it to WHERE order_date >= '2024-03-01 00:00:00' AND order_date < '2024-03-02 00:00:00'. That's the real fix.
Use FORCE INDEX as a test
Only for debugging, not for production:
```
SELECT * FROM orders FORCE INDEX (idx_order_date) WHERE order_date > '2024-03-01';
```
If it returns fast, you know the index works. Then figure out why the optimizer doesn't trust it.
Optimize the table
If the table has frequent inserts and deletes, run:
```
OPTIMIZE TABLE orders;
```
This rebuilds the index and defragments it. Do this during low traffic.

Alternative fixes if the main steps fail

If updating stats and rewriting the query didn't help, try these:

Change the index order – If your index is on (order_date, customer_id) but you filter only on customer_id, the index is useless. Create a separate index on customer_id.
Use a covering index – Include all columns from the SELECT in the index. Example: CREATE INDEX idx_covering ON orders (order_date, total_amount). Then MySQL can read directly from the index.
Partition the table – For really large tables (millions of rows), partitioning by date can force the optimizer to skip irrelevant partitions. Only do this if you're comfortable with partition management.
Lower the optimizer_search_depth – In rare cases, MySQL's optimizer gets confused. Setting SET optimizer_search_depth = 5 can help. But don't touch this unless you know what you're doing.

One client had a query that took 8 seconds. ANALYZE TABLE fixed it instantly. Another client had a function wrapping a column. That took an hour to find because the function was in a join condition. Always check the WHERE clause first.

How to prevent this from happening again

Three things you can do right now:

Schedule ANALYZE TABLE weekly – If your data changes a lot, set a cron job to run ANALYZE TABLE on the busiest tables. In MySQL 8.0, you can also enable innodb_stats_auto_recalc.
Never wrap indexed columns in functions – Not in WHERE, not in JOIN, not in ORDER BY. If you need date-based queries, use range conditions.
Monitor slow query log regularly – Enable the slow query log and check it daily. Catch issues before users complain.

Remember, indexes are tools. They work only when the query respects them. Keep your stats fresh, your queries clean, and your EXPLAIN plan honest. That's the real fix.