When an index is not used
50 min · Advanced
Need help? Ask a tutor on WhatsApp (opens in a new tab)Creating an index does not mean MySQL will use it. The optimizer uses an index only when the query is written so that the sorted order can help, and only when it expects the index to be cheaper than a scan. Most "my index is ignored" problems come from a handful of patterns.
Small tables and many matching rows
An index is not always the cheapest plan. The script below runs a query that matches most of the table, where MySQL picks a full scan on purpose.
Example 1 · Many matching rows: the optimizer prefers a scan
SET SESSION cte_max_recursion_depth = 50000;
CREATE TABLE upi_payments ( payment_id INT PRIMARY KEY, payer_vpa VARCHAR(40) NOT NULL, mobile VARCHAR(10) NOT NULL, amount DECIMAL(10,2) NOT NULL, status VARCHAR(10) NOT NULL, paid_at DATETIME NOT NULL);
INSERT INTO upi_paymentsWITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 50000)SELECT n, CONCAT('user', n % 5000, '@okbank'), CONCAT('98', LPAD(n * 7 % 100000000, 8, '0')), 10 + (n * 37) % 5000, IF(n % 50 = 0, 'FAILED', 'SUCCESS'), TIMESTAMP '2026-01-01 00:00:00' + INTERVAL (n * 600) SECONDFROM seq;
CREATE INDEX idx_upi_amount ON upi_payments (amount);ANALYZE TABLE upi_payments;
-- About 98% of payments are above Rs. 100EXPLAIN SELECT * FROM upi_payments WHERE amount > 100;
-- About 2% are above Rs. 4,900EXPLAIN SELECT * FROM upi_payments WHERE amount > 4900;+-----------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------------------+---------+----------+----------+ | practice.upi_payments | analyze | status | OK | +-----------------------+---------+----------+----------+ +----+-------------+--------------+------------+------+----------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+------+----------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | upi_payments | NULL | ALL | idx_upi_amount | NULL | NULL | NULL | 49872 | 50.00 | Using where | +----+-------------+--------------+------------+------+----------------+------+---------+------+-------+----------+-------------+ +----+-------------+--------------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | upi_payments | NULL | range | idx_upi_amount | idx_upi_amount | 5 | NULL | 1090 | 100.00 | Using index condition | +----+-------------+--------------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
What this code does
- For
amount > 100,possible_keyslistsidx_upi_amountbutkeyisNULLandtypeisALL: going through the index for 49,000 rows, with a second lookup for each, would cost more than reading the table once. - For
amount > 4900the same index is chosen as arangeof about 1,090 rows. Same index, same column: only the share of matching rows changed. Plans are estimates, so yours may differ slightly.
The optimizer compares the estimated cost of each plan and picks the cheapest. Reading through a secondary index costs more per row than a scan: for each entry, MySQL makes a second lookup in the clustered index to fetch the row. So when a query matches a large share of the table, often somewhere above 20 to 30% (the exact point varies), a full scan is cheaper, and MySQL chooses it on purpose.
The same is true for tiny tables. A table that fits in one or two pages is read in one go; jumping through an index saves nothing, and MySQL may skip it. This is not a bug to fix. If a query really needs most of the rows, a scan is the right plan. The fix, when one is needed, is usually a narrower condition, or a covering index (the last section).
Order of execution
How the optimizer decides
Put the optimizer's steps in order.
Functions on the column
A condition is sargable when it is written so an index can be used: the column appears bare, not inside a function or calculation. paid_at >= '2026-03-01' is sargable; DATE(paid_at) = '2026-03-01' is not.
Wrapping a column in a function hides the index. The script below compares a function on the column with a plain range on the same column.
Example 2 · A function on the column hides the index
SET SESSION cte_max_recursion_depth = 50000;
CREATE TABLE upi_payments ( payment_id INT PRIMARY KEY, payer_vpa VARCHAR(40) NOT NULL, mobile VARCHAR(10) NOT NULL, amount DECIMAL(10,2) NOT NULL, status VARCHAR(10) NOT NULL, paid_at DATETIME NOT NULL);
INSERT INTO upi_paymentsWITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 50000)SELECT n, CONCAT('user', n % 5000, '@okbank'), CONCAT('98', LPAD(n * 7 % 100000000, 8, '0')), 10 + (n * 37) % 5000, IF(n % 50 = 0, 'FAILED', 'SUCCESS'), TIMESTAMP '2026-01-01 00:00:00' + INTERVAL (n * 600) SECONDFROM seq;
CREATE INDEX idx_upi_vpa ON upi_payments (payer_vpa);CREATE INDEX idx_upi_mobile ON upi_payments (mobile);CREATE INDEX idx_upi_paid_at ON upi_payments (paid_at);
-- The function runs on every row: full scanSELECT COUNT(*) AS payments, SUM(amount) AS totalFROM upi_paymentsWHERE DATE(paid_at) = '2026-03-01';SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;
-- Same answer with the column left bare: index rangeSELECT COUNT(*) AS payments, SUM(amount) AS totalFROM upi_paymentsWHERE paid_at >= '2026-03-01' AND paid_at < '2026-03-02';SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;
-- Or index the expression itself (MySQL 8.0.13+)CREATE INDEX idx_upi_paid_date ON upi_payments ((DATE(paid_at)));
SELECT COUNT(*) AS payments, SUM(amount) AS totalFROM upi_paymentsWHERE DATE(paid_at) = '2026-03-01';SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;+----------+-----------+ | payments | total | +----------+-----------+ | 144 | 379080.00 | +----------+-----------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 50000 | 1 | +---------------+---------------+ +----------+-----------+ | payments | total | +----------+-----------+ | 144 | 379080.00 | +----------+-----------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 144 | 1 | +---------------+---------------+ +----------+-----------+ | payments | total | +----------+-----------+ | 144 | 379080.00 | +----------+-----------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 144 | 1 | +---------------+---------------+
What this code does
- All three queries return the same 144 payments of 1 March. With
DATE(paid_at), MySQL examined all 50,000 rows, because the index holdspaid_atvalues, notDATE(paid_at)values. - The rewritten range and the functional index both examine just 144 rows. Prefer the rewrite: it needs no extra index, and the existing
idx_upi_paid_atalso serves other date ranges.
An index is sorted by the column's stored value. When the WHERE clause wraps the column in a function or calculation, such as DATE(paid_at), YEAR(order_date), LOWER(email) or amount + 50, MySQL would have to compute the function for every row before comparing, so it cannot jump into the sorted index.
The fix is to leave the column bare and move the work to the other side. amount + 50 > 1000 becomes amount > 950, and a date test becomes a half-open range (>= start, < next day), which catches every time of the day without guessing the last second. When the function cannot be removed, MySQL 8.0.13 and later can index the expression itself: a functional index such as CREATE INDEX idx ON t ((DATE(paid_at))), with double brackets.
Leading wildcards in LIKE
A leading wildcard is a % at the start of a LIKE pattern, as in LIKE '%42@okbank'. The text has no fixed start, so MySQL cannot range-scan the sorted index and must read every entry.
A fixed start lets MySQL use the sorted index; a leading % does not. The script below compares LIKE 'user42@%' with LIKE '%42@okbank'.
Example 3 · Leading wildcard versus fixed prefix
SET SESSION cte_max_recursion_depth = 50000;
CREATE TABLE upi_payments ( payment_id INT PRIMARY KEY, payer_vpa VARCHAR(40) NOT NULL, mobile VARCHAR(10) NOT NULL, amount DECIMAL(10,2) NOT NULL, status VARCHAR(10) NOT NULL, paid_at DATETIME NOT NULL);
INSERT INTO upi_paymentsWITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 50000)SELECT n, CONCAT('user', n % 5000, '@okbank'), CONCAT('98', LPAD(n * 7 % 100000000, 8, '0')), 10 + (n * 37) % 5000, IF(n % 50 = 0, 'FAILED', 'SUCCESS'), TIMESTAMP '2026-01-01 00:00:00' + INTERVAL (n * 600) SECONDFROM seq;
CREATE INDEX idx_upi_vpa ON upi_payments (payer_vpa);CREATE INDEX idx_upi_mobile ON upi_payments (mobile);CREATE INDEX idx_upi_paid_at ON upi_payments (paid_at);
-- Ends with: every entry must be checkedSELECT COUNT(*) AS payments, SUM(amount) AS totalFROM upi_paymentsWHERE payer_vpa LIKE '%42@okbank';SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;
-- Starts with: a range scan on the sorted indexSELECT COUNT(*) AS payments, SUM(amount) AS totalFROM upi_paymentsWHERE payer_vpa LIKE 'user42@%';SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;+----------+------------+ | payments | total | +----------+------------+ | 500 | 1257000.00 | +----------+------------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 50000 | 1 | +---------------+---------------+ +----------+----------+ | payments | total | +----------+----------+ | 10 | 15640.00 | +----------+----------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 10 | 1 | +---------------+---------------+
What this code does
'%42@okbank'matches 50 users (user42,user142, ...user4942), 500 payments, and MySQL examined all 50,000 rows to find them.'user42@%'matches onlyuser42@okbank: MySQL jumped to the prefix inidx_upi_vpaand examined its 10 payments. The two queries answer different questions, which is the point: design searches around prefixes when you can.
LIKE 'user42@%' fixes the start of the text, so MySQL can range-scan the sorted index from user42@ to the end of that prefix, like finding every word starting with "cat" in a dictionary. LIKE '%42@okbank' fixes only the end. Words ending in "cat" are scattered over the whole dictionary, so MySQL must read every entry.
A wildcard in the middle ('user%@okbank') still lets MySQL use the fixed part before it. For "contains" searches on real text, such as product names, MySQL has FULLTEXT indexes; for "ends with" searches, some teams store a reversed copy of the column and index that.
Type mismatches and OR conditions
A type mismatch is a comparison between values of different types, such as a VARCHAR column and a number. MySQL converts every stored text to a number first, so it cannot use the index on the text.
A text column compared with a number, such as WHERE mobile = 9800000294, forces a full scan, and so can an OR with an unindexed branch. The scripts below show both, and the fix for each.
Example 4 · A number compared with a VARCHAR column
SET SESSION cte_max_recursion_depth = 50000;
CREATE TABLE upi_payments ( payment_id INT PRIMARY KEY, payer_vpa VARCHAR(40) NOT NULL, mobile VARCHAR(10) NOT NULL, amount DECIMAL(10,2) NOT NULL, status VARCHAR(10) NOT NULL, paid_at DATETIME NOT NULL);
INSERT INTO upi_paymentsWITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 50000)SELECT n, CONCAT('user', n % 5000, '@okbank'), CONCAT('98', LPAD(n * 7 % 100000000, 8, '0')), 10 + (n * 37) % 5000, IF(n % 50 = 0, 'FAILED', 'SUCCESS'), TIMESTAMP '2026-01-01 00:00:00' + INTERVAL (n * 600) SECONDFROM seq;
CREATE INDEX idx_upi_vpa ON upi_payments (payer_vpa);CREATE INDEX idx_upi_mobile ON upi_payments (mobile);CREATE INDEX idx_upi_paid_at ON upi_payments (paid_at);
-- mobile is VARCHAR: comparing it with a number scans everythingSELECT payment_id, payer_vpa FROM upi_payments WHERE mobile = 9800000294;SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;
-- Quoted, the value is text like the column: index lookupSELECT payment_id, payer_vpa FROM upi_payments WHERE mobile = '9800000294';SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;
-- Why MySQL cannot use the index for the number: these are all trueSELECT '09800000294' = 9800000294 AS leading_zero, '42' = 42 AS text_vs_int, 'abc' = 0 AS text_becomes_zero;+------------+---------------+ | payment_id | payer_vpa | +------------+---------------+ | 42 | user42@okbank | +------------+---------------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 50000 | 1 | +---------------+---------------+ +------------+---------------+ | payment_id | payer_vpa | +------------+---------------+ | 42 | user42@okbank | +------------+---------------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 1 | 1 | +---------------+---------------+ +--------------+-------------+-------------------+ | leading_zero | text_vs_int | text_becomes_zero | +--------------+-------------+-------------------+ | 1 | 1 | 1 | +--------------+-------------+-------------------+
What this code does
- The unquoted number examined 50,000 rows; the quoted text examined 1. Same row, same index, one pair of quotes.
- The last query shows the reason: text compared with a number is converted to a number, so
'09800000294'equals 9800000294 and even'abc'equals 0. Many different texts match one number, so a sorted text index cannot narrow the search.
Example 5 · OR needs every branch indexed
SET SESSION cte_max_recursion_depth = 50000;
CREATE TABLE upi_payments ( payment_id INT PRIMARY KEY, payer_vpa VARCHAR(40) NOT NULL, mobile VARCHAR(10) NOT NULL, amount DECIMAL(10,2) NOT NULL, status VARCHAR(10) NOT NULL, paid_at DATETIME NOT NULL);
INSERT INTO upi_paymentsWITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 50000)SELECT n, CONCAT('user', n % 5000, '@okbank'), CONCAT('98', LPAD(n * 7 % 100000000, 8, '0')), 10 + (n * 37) % 5000, IF(n % 50 = 0, 'FAILED', 'SUCCESS'), TIMESTAMP '2026-01-01 00:00:00' + INTERVAL (n * 600) SECONDFROM seq;
CREATE INDEX idx_upi_vpa ON upi_payments (payer_vpa);CREATE INDEX idx_upi_mobile ON upi_payments (mobile);CREATE INDEX idx_upi_paid_at ON upi_payments (paid_at);
-- Both columns indexed: two lookups, mergedSELECT SUM(amount) AS total FROM upi_paymentsWHERE payer_vpa = 'user42@okbank' OR mobile = '9800000294';SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;
-- status has no index: that branch forces a full scanSELECT SUM(amount) AS total FROM upi_paymentsWHERE payer_vpa = 'user42@okbank' OR status = 'FAILED';SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;+----------+ | total | +----------+ | 15640.00 | +----------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 11 | 1 | +---------------+---------------+ +------------+ | total | +------------+ | 2500640.00 | +------------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 50000 | 1 | +---------------+---------------+
What this code does
- With indexes on
payer_vpaandmobile, MySQL used an index merge: 10 payments ofuser42plus the one payment from that mobile, 11 rows examined.EXPLAINshows it astype = index_mergewithUsing union(...). - Adding
OR status = 'FAILED'on an unindexed column turned the whole query into a 50,000-row scan, even thoughpayer_vpais indexed. Indexstatus, or split the query into two parts joined byUNION.
Quote the value: WHERE mobile = '9800000294'. The other way round is safe: WHERE payment_id = '42' on an INT column converts the one constant, and the index still works. OR across different columns needs every branch to be indexed. With indexes on both columns, MySQL can run two index lookups and merge them (index_merge). If any branch has no usable index, that branch alone forces a full scan, so the whole query becomes a scan.
IN ('a', 'b')on one column is fine: it is a set of ranges on the same index.- Joins have the same trap: joining an
INTcolumn to aVARCHARcolumn, or columns with different character sets, can stop the index on the inner table.
Covering indexes
A covering index is an index that contains every column a query reads. MySQL then answers from the index alone and never fetches the table rows; EXPLAIN shows Using index.
A covering index can win where a normal index loses. The script below compares a normal index with a covering index on the same range query.
Example 6 · A covering index wins where a normal index loses
SET SESSION cte_max_recursion_depth = 50000;
CREATE TABLE upi_payments ( payment_id INT PRIMARY KEY, payer_vpa VARCHAR(40) NOT NULL, mobile VARCHAR(10) NOT NULL, amount DECIMAL(10,2) NOT NULL, status VARCHAR(10) NOT NULL, paid_at DATETIME NOT NULL);
INSERT INTO upi_paymentsWITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 50000)SELECT n, CONCAT('user', n % 5000, '@okbank'), CONCAT('98', LPAD(n * 7 % 100000000, 8, '0')), 10 + (n * 37) % 5000, IF(n % 50 = 0, 'FAILED', 'SUCCESS'), TIMESTAMP '2026-01-01 00:00:00' + INTERVAL (n * 600) SECONDFROM seq;
CREATE INDEX idx_upi_vpa ON upi_payments (payer_vpa);CREATE INDEX idx_upi_mobile ON upi_payments (mobile);CREATE INDEX idx_upi_paid_at ON upi_payments (paid_at);
ANALYZE TABLE upi_payments;
-- February onwards is about 90% of the table: a scanEXPLAIN SELECT COUNT(*), SUM(amount) FROM upi_paymentsWHERE paid_at >= '2026-02-01';
-- The index now holds both columns the query readsCREATE INDEX idx_upi_paid_amount ON upi_payments (paid_at, amount);
EXPLAIN SELECT COUNT(*), SUM(amount) FROM upi_paymentsWHERE paid_at >= '2026-02-01';+-----------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------------------+---------+----------+----------+ | practice.upi_payments | analyze | status | OK | +-----------------------+---------+----------+----------+ +----+-------------+--------------+------------+------+-----------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+------+-----------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | upi_payments | NULL | ALL | idx_upi_paid_at | NULL | NULL | NULL | 49883 | 50.00 | Using where | +----+-------------+--------------+------------+------+-----------------+------+---------+------+-------+----------+-------------+ +----+-------------+--------------+------------+-------+-------------------------------------+---------------------+---------+------+-------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+-------------------------------------+---------------------+---------+------+-------+----------+--------------------------+ | 1 | SIMPLE | upi_payments | NULL | range | idx_upi_paid_at,idx_upi_paid_amount | idx_upi_paid_amount | 5 | NULL | 24941 | 100.00 | Using where; Using index | +----+-------------+--------------+------------+-------+-------------------------------------+---------------------+---------+------+-------+----------+--------------------------+
What this code does
- With only
idx_upi_paid_at, the query would need a second lookup foramounton about 45,000 rows, so MySQL scans the table (type = ALL) although the index is listed inpossible_keys. (paid_at, amount)covers the query:ExtrashowsUsing index, and MySQL range-scans the index without touching the table. The rows are still many, but each one is read from a smaller structure with no second lookup.
Not fetching the table rows removes the expensive part of a secondary index read, the second lookup per row, so the optimizer will use a covering index even for ranges with many rows, where a normal index would lose to a scan. A covering index is also smaller than the table, so a scan of it reads fewer pages.
The catch: SELECT * can never be covered by a secondary index. List the columns you need, and add a column to an index only for a query that runs often enough to be worth it.
Next step
Check your understanding
12 quick questions