Telling apart 3 reasons MySQL isn't using your index

You are reading the slow query log in CloudWatch Logs or a similar tool, and one query has a Rows_examined (rows read) about equal to the table's row count, or larger, while its Rows_sent (rows returned) is only a few. SHOW INDEX shows an index on the column used in the condition. Running EXPLAIN on the same query in a staging environment restored from a backup gives ALL in the type column: the whole table is read.

Of the reasons MySQL can't use an index, I cover the following three.

The first is a query that passes a datetime column (created_at) through DATE() to filter by date. I call this the "function" case.

SELECT * FROM members WHERE DATE(created_at) = '2010-01-01'; 

The second is a query on a member number made only of digits (code), stored in a VARCHAR column and searched with an unquoted number. I call this "type mismatch".

SELECT * FROM members WHERE code = 1000042; 

The third joins two tables on the member number, where the column's character set is utf8mb4 on one side (members.code) and utf8mb3 on the other (b_mb3_unicode.member_code). I call this "character set mismatch". utf8mb4 and utf8mb3 are both MySQL character sets for UTF-8; one stores a character in up to 4 bytes, the other in up to 3. Writing utf8 as the character set gives you utf8mb3.¹

SELECT a.code, b.payload FROM members a JOIN b_mb3_unicode b ON b.member_code = a.code WHERE a.account_id = 42; 

The function case is visible in the query. Type mismatch and character set mismatch are not. In both, the condition is a plain equality, and the difference lies in the columns' types and character sets.

I set up tables and queries that trigger each of the three causes on MySQL 8.4 and 8.0, and checked the output of EXPLAIN and SHOW WARNINGS, along with Rows_examined in the slow query log.²

Mapping EXPLAIN output to the cause

The three are told apart by possible_keys in EXPLAIN, by warning 1739 from a SHOW WARNINGS run right after it, and by what the column named in 1739 is compared with.

possible_keys

possible_keys lists the indexes the optimizer picked as candidates. In a join, look at the row for the table whose type is ALL. Of the three, only the function case gives NULL in possible_keys. When a condition passes a column through a function, that column's index is not even a candidate.

In type mismatch and character set mismatch, the index does appear in possible_keys. In these two, warning 1739 shows that the index can't be used.

Warning 1739

MySQL emits warning 1739 when it decides it can't use an index because of a type or collation conversion. Running the query and then SHOW WARNINGS does not show it, and the slow query log does not record it.³ Run EXPLAIN, then SHOW WARNINGS right after it in the same session.

Below, the VARCHAR column code is compared with a number, and SHOW WARNINGS returns two 1739 rows. ref and range are names of access methods that find rows through an index, and both rows can appear for the same index. The final 1003 row is the query after MySQL rewrote it; I shortened the column list to ....

EXPLAIN SELECT * FROM members WHERE code = 1000042; +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | members | NULL | ALL | idx_code | NULL | NULL | NULL | 298796 | 10.00 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+ SHOW WARNINGS; +---------+------+-------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------------------------+ | Warning | 1739 | Cannot use ref access on index 'idx_code' due to type or collation conversion on field 'code' | | Warning | 1739 | Cannot use range access on index 'idx_code' due to type or collation conversion on field 'code' | | Note | 1003 | /* select#1 */ select ... from `lab`.`members` where (`lab`.`members`.`code` = 1000042) | +---------+------+-------------------------------------------------------------------------------------------------+ 

What the column is compared with

The 1739 message reads "type or collation conversion" for both type mismatch and character set mismatch. To separate them, find the column named after on field and look at what the query compares it with. In the example above, code is compared with the unquoted number 1000042, so it is a type mismatch. If the other side is a column, compare the two columns' character sets with SHOW CREATE TABLE. If they differ, it is a character set mismatch.

Each cause shows up as follows.

How each cause happens

Function

SELECT * FROM members WHERE DATE(created_at) = '2010-01-01'; # Query_time: 0.047168 Lock_time: 0.000003 Rows_sent: 48 Rows_examined: 300000 

The index holds the values of created_at, not the values of DATE(created_at). MySQL can't use the index to find the rows where DATE(created_at) is '2010-01-01', so it passes every row's created_at through DATE() and compares the result.

To fix it, rewrite the condition as the range created_at >= '2010-01-01' AND created_at < '2010-01-02'. It returns the same rows, and the index can find them. To fix it without changing the query, add a functional index on DATE(created_at).⁴ Either way, the number of rows read equals the number of rows returned.

Type mismatch

SELECT * FROM members WHERE code = 1000042; # Query_time: 0.078791 Lock_time: 0.000002 Rows_sent: 1 Rows_examined: 300000 

When the string column code is compared with the number 1000042, MySQL converts the values of code to numbers and compares those. '1', ' 1', and '00001' all become the number 1. The index orders code as strings, so the rows matching the number 1000042 are not in one place in the index. MySQL therefore reads every row.⁵

This happens when application code passes the value as a number.

To fix it, pass the value as a string (code = '1000042').

Character set mismatch

SELECT a.code, b.payload FROM members a JOIN b_mb3_unicode b ON b.member_code = a.code WHERE a.account_id = 42; # Query_time: 0.945879 Lock_time: 0.000003 Rows_sent: 5 Rows_examined: 600000 

When b.member_code (utf8mb3) is compared with a.code (utf8mb4), MySQL converts the values of b.member_code to utf8mb4 and compares those. b's index is ordered by the utf8mb3 values, so it can't be used to find the rows.⁵ The index on code in a is usable, so MySQL reads all of b and, for each row of b, looks up a through the index. b also has 300,000 rows. The 300,000 rows read from b plus the 300,000 looked up in a make Rows_examined 600,000.

A character set is set per table and per column, and whatever was specified when the table was created stays. With nothing specified, it is the database default at that time.⁶ If you used to create tables in utf8 (utf8mb3) and at some point switched to utf8mb4, both kinds of tables end up side by side in the same database. Converting an existing table to utf8mb4 requires rebuilding it, and writes are blocked during the rebuild.⁷

To fix it, convert the utf8mb3 columns to utf8mb4 during a window when you can stop writes.

Checking on staging

Restore a production backup onto a staging server running the same MySQL version as production. Run EXPLAIN on the statement from the slow query log as-is, then run SHOW WARNINGS, and you get the same verdict as in production.

To read the slow query log outside production, have it written to a file (log_output set to FILE) and shipped to CloudWatch Logs. Aurora MySQL 3 defaults to FILE.⁸ RDS for MySQL defaults to TABLE, which puts the log in the mysql.slow_log table in production, so reading it means running queries against production.⁹

The slow query log keeps statements with their values embedded. Numbers stay unquoted, so running the logged statement as-is compares values with the same types as in production. Queries run as server-side prepared statements are also logged with their values filled in.¹⁰

Whether an index can be used depends on the query's expressions and the columns' types and character sets, not on the table's row count or statistics. Below is EXPLAIN for the type mismatch query SELECT * FROM members WHERE code = 1000042, on the original table and on the table just after restoring it with mysqldump. The top row is the original table and the bottom row the restored one. rows (the estimated row count) differs, but type, possible_keys, and key are the same. So is warning 1739 in SHOW WARNINGS.

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | | 1 | SIMPLE | members | NULL | ALL | idx_code | NULL | NULL | NULL | 298796 | 10.00 | Using where | | 1 | SIMPLE | members | NULL | ALL | idx_code | NULL | NULL | NULL | 10 | 10.00 | Using where | 

mysqldump writes a column's character set into CREATE TABLE when it differs from the table default. So after the restore, the columns have the same character sets as in production. Below is part of the CREATE TABLE in the dump: the table default is utf8mb4, and the member_code column is utf8mb3.

 `member_code` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL, ... ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; 

1. The utf8mb3 Character Set in the MySQL 8.4 Reference Manual. ↩

2. Verified with MySQL 8.4.11 and 8.0.46 in Docker on a GitHub Actions ubuntu-24.04 runner. The 300,000-row table members (utf8mb4, utf8mb4_unicode_ci) has columns such as a VARCHAR member number, a datetime, and an email address, each with its own index. As join partners, I created two tables with an indexed column holding the same values as the member number in members: one with collation utf8mb4_unicode_ci and one with utf8mb3_unicode_ci. The scripts and results are in show-your-work and the GitHub Actions run. ↩

3. In the MySQL 8.4.11 source, 1739 is emitted only during EXPLAIN and when sql_safe_updates is enabled (ref check, range check). ↩

4. Functional Key Parts in CREATE INDEX in the MySQL 8.4 Reference Manual. A functional index is used when the expression in the query matches the expression in the index. ↩

5. How MySQL Uses Indexes in the MySQL 8.4 Reference Manual. It states that an index can't be used when a string column is compared with a number, or when columns with different character sets are compared. ↩

6. Specifying Character Sets and Collations in the MySQL 8.4 Reference Manual. ↩

7. Online DDL Operations in the MySQL 8.4 Reference Manual. Converting the character set rebuilds the table and does not permit concurrent DML. ↩

8. Overview of Aurora MySQL database logs in the Amazon Aurora User Guide. The default log_output for Aurora MySQL 2 and 3 is FILE, and shipping to CloudWatch Logs requires FILE. ↩

9. Overview of RDS for MySQL database logs in the Amazon RDS User Guide. The default log_output is TABLE, and slow queries are written to the mysql.slow_log table. ↩

10. The code in the MySQL 8.4.11 source that fills a prepared statement's values into the statement text for logging (sql_prepare.cc). ↩