Added an Index but the MySQL Query Is Still Slow? Check EXPLAIN to See If It's Used

You found a slow query, added an index on the column in its WHERE clause, and the page is just as slow as before. It is natural to assume MySQL picks up a new index automatically, but the optimizer (the part of MySQL that decides how to run a query) only uses an index when the query is written in a way that lets it. Instead of guessing, you can ask MySQL directly with EXPLAIN and get a clear yes or no. This is one of the most common follow-up questions at a support desk after someone tunes a database. In most of these cases the index is fine. The query just cannot use it, and EXPLAIN shows that within a few seconds.

Run EXPLAIN on the exact query, not a cleaned-up version

Start with the real query your site runs, including the literal values. The optimizer's choice can change depending on the values in the WHERE clause, so a simplified copy can give you a different answer. If you do not have the query yet, the slow query log is the usual source; there is a separate walkthrough on reviewing the MySQL slow query log. Plain EXPLAIN does not execute the query. It only shows the plan, so it is safe to run on a production database. Log in with the mysql client, confirm the index actually exists, then explain the query:

SQL

-- List indexes on the table to confirm the new one is there
SHOW INDEX FROM orders;

-- Show the plan; \G prints one field per line, which is easier to read
EXPLAIN SELECT id, total FROM orders WHERE DATE(created_at) = '2026-01-15'\G

In this example there is an index on created_at, but the query wraps that column in DATE(). Here is what the result typically looks like: Illustrative output format:

TEXT

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200000
     filtered: 100.00
        Extra: Using where

The numbers are illustrative, but the pattern is the one to recognize: key is NULL and type is ALL. MySQL is reading the whole table and checking every row, exactly as if the index did not exist.

Which EXPLAIN columns actually answer the question

You do not need to understand every field. Five of them tell you almost everything. possible_keys lists the indexes the optimizer considered usable for this query. If your new index is not listed here, the query shape is preventing it from even being a candidate. key is the index MySQL actually chose. This is the column that answers the title question. NULL means no index was used. type describes how rows are accessed. Roughly from best to worst: const and eq_ref (a single row via a unique key), ref (lookup by a non-unique index value), range (a bounded slice of the index, such as a date range), index (reading the entire index), and ALL (a full table scan). An ALL on a large table is usually the reason the page is slow. rows is the optimizer's estimate of how many rows it will examine. It is based on index statistics, so treat it as a rough figure, not an exact count. What matters is the comparison: if the table has a large number of rows and the query should only return a handful, a rows value close to the table size means the index is not narrowing anything down. Extra adds detail. Using index means the query was answered from the index alone, which is good. Using filesort and Using temporary mean MySQL had to sort or build a temporary table after reading, which often costs more than the lookup itself on big result sets. There is one confusing case worth knowing. If possible_keys shows your index but key is still NULL, the optimizer saw the index and decided a full scan would be cheaper. That usually happens when the column has very few distinct values (a status column with three possible values, for example), when the query would match a large share of the table anyway, or when the index statistics are stale.

The three query patterns that hide an index

When possible_keys is NULL even though the index exists, the cause is almost always in how the WHERE clause is written. A function on the indexed column is the first suspect. An index stores the raw column values in sorted order. Expressions like DATE(created_at), YEAR(created_at), LOWER(email), or TRIM(sku) produce new values that are not in that sorted structure, so MySQL has to compute them for every row. The fix is to move the work to the other side of the comparison, for example turning a single day into a range on the raw column. On MySQL 8.0.13 and later you can also create a functional index on the expression itself, and MariaDB can achieve something similar with an indexed generated column, but rewriting the query is usually simpler. A leading wildcard in LIKE is the second. LIKE 'john%' can use an index because MySQL can jump to the part of the sorted index that starts with john. LIKE '%@gmail.com' cannot, because the matching values are scattered throughout the index. This pattern shows up often in admin search boxes and custom report pages. If you really need contains-style search on a large table, a FULLTEXT index is the usual direction, but it has its own matching rules, so test it against what users actually type. A data type or collation mismatch is the third, and the hardest to spot by eye. If phone is a VARCHAR column and the query says WHERE phone = 812345678 without quotes, MySQL converts each stored string to a number to compare them, and the index on phone cannot be used. The reverse, a numeric column compared to a quoted string, is usually handled fine because only the literal gets converted. A related case is joining two tables whose columns use different character sets or collations, such as one table in utf8mb3 and another in utf8mb4. This tends to appear after sites have been migrated or partly rebuilt over the years, and EXPLAIN shows it as a full scan on one side of the join even though both columns are indexed. Here are the before and after versions to run side by side:

SQL

-- Before: function on the column hides the index
EXPLAIN SELECT id FROM orders WHERE DATE(created_at) = '2026-01-15'\G

-- After: same rows, expressed as a range the index can use
EXPLAIN SELECT id FROM orders
WHERE created_at >= '2026-01-15' AND created_at < '2026-01-16'\G

-- Before: VARCHAR column compared with a number
EXPLAIN SELECT id FROM customers WHERE phone = 812345678\G

-- After: compare string to string
EXPLAIN SELECT id FROM customers WHERE phone = '812345678'\G

-- Measure real execution (MySQL 8.0.18+). This DOES run the query.
EXPLAIN ANALYZE SELECT id FROM orders
WHERE created_at >= '2026-01-15' AND created_at < '2026-01-16';

In the after versions you should see key filled in with your index name, type change from ALL to range or ref, and rows drop sharply.

Verify the change instead of trusting the plan

A better-looking plan is a strong signal, but confirm it with actual execution before you call the job done.

Save the original EXPLAIN output so you have something concrete to compare against.
Apply the change, whether that is a query rewrite, a new index, or a type fix.
Run EXPLAIN again and compare key, type, and rows.
Run EXPLAIN ANALYZE on MySQL 8.0.18 or later, or ANALYZE SELECT ... on MariaDB. These report actual rows read and time spent. Be aware they really execute the query, so avoid running them on very heavy queries during peak traffic.
Run the query itself twice and compare the elapsed time the mysql client prints. The first run may be slower because data is being read from disk into memory.

If possible_keys lists your index but key stays NULL, you can test whether the optimizer is making a bad call by adding FORCE INDEX (index_name) after the table name and explaining the query again. If the forced plan is clearly faster in EXPLAIN ANALYZE, the index statistics may be outdated, and running ANALYZE TABLE orders; refreshes them. On InnoDB this is usually quick, but it still takes brief locks, so on a very busy table it is safer to run it outside peak hours. Treat FORCE INDEX as a diagnostic tool rather than a permanent fix, because a hint that helps today can hurt later when the data changes. If the forced plan is not faster, the optimizer was probably right. A full scan can genuinely be the cheapest option when the query matches a large part of the table. At that point the real issue is either that the query asks for too much data or that the server is short on memory for the dataset. If you see heavy disk reads alongside queries that already use good indexes, the guide on checking whether the InnoDB buffer pool is too small is the next place to look.

A practical habit for every index you add

Treat EXPLAIN as part of adding the index, not something you check later. Run it before the change, run it after, and confirm that key names your index and rows dropped. If key stays NULL, look at the WHERE clause before blaming the index. Check for a function wrapped around the column, a wildcard at the start of a LIKE pattern, and a value whose type or collation does not match the column. Those three checks take a few minutes and usually explain why a correct index made no difference.