Why MySQL Ignored My Index: Charset Mismatch Triggers Full Table Scans

Background

The author encountered a puzzling performance problem: joining a large table (about one million rows) with a tiny table should be fast, but the query took 5‑6 seconds despite an index on the join column.

Reproducing the Scenario

user_info

– a mock table with three columns. user_score – contains uid that matches user_info.uid.

Data was mocked to about 3 million rows, and the join query was:

SELECT * FROM user_score us
INNER JOIN user_info ui ON us.uid = ui.uid
WHERE us.id = 5;

Running EXPLAIN showed that user_info was scanned fully, ignoring its index.

Initial Troubleshooting

Various attempts—changing join order, using subqueries—did not make the index work. A single‑table query on user_score used the index correctly, indicating the problem lay in the join.

Root Cause Discovery

Inspecting the table definitions revealed a character‑set mismatch: the large table used utf8mb4 while the small table used utf8. Because the indexed column participated in a character‑set conversion, MySQL could not use the index and performed a full table scan.

Running EXPLAIN EXTENDED and SHOW WARNINGS displayed a condition like:

((('111111111' = CONVERT(`test`.`ui`.`uid` USING utf8mb4))))

This conversion forced a full scan.

Solution

The fix was to alter the small table’s character set to match the large table’s utf8mb4 charset, after which the join used the index and the query executed instantly.

mysql> SELECT * FROM user_score us
INNER JOIN user_info ui ON us.uid = ui.uid
WHERE us.id = 5;
+----+-----------+-------+----+-----------+--------+
| id | uid       | score | id | uid       | name   |
+----+-----------+-------+----+-----------+--------+
|  5 | 111111111 | 100   |  1 | 111111111 | tanglei|
|  5 | 111111111 | 100   | 3685399 | 111111111 | tanglei |
... (output truncated) ...
2 rows in set (0.00 sec)

EXPLAIN:
+----+-------------+-------+------+-------------------+-----------+---------+------+------+
| id | select_type | table | type | possible_keys     | key       | key_len | ref  | rows |
+----+-------------+-------+------+-------------------+-----------+---------+------+------+
| 1  | SIMPLE      | us    | const| PRIMARY,index_uid | PRIMARY   | 4       | const| 1    |
| 1  | SIMPLE      | ui    | ref  | index_uid         | index_uid | 194     | const| 6    |
+----+-------------+-------+------+-------------------+-----------+---------+------+------+

Takeaways

Never let indexed columns participate in implicit conversions; ensure matching character sets.

Historical tables may have inconsistent collations; enforce development standards for charset consistency.

Use EXPLAIN EXTENDED (or EXPLAIN in MySQL 8.0) and SHOW WARNINGS to spot hidden conversions.

Adopt a fail‑fast approach: reject joins where column types or charsets differ.

The article emphasizes that proper schema design and consistent character sets are crucial for index utilization and query performance.