MySQL Index Not Used Due to Implicit Character Set Conversion and How to Fix It

Introduction

After the 2021 Spring Festival, a colleague reported that a MySQL query was not using an index. The initial suspicion of function operations or implicit numeric conversion was ruled out, leading to the discovery that an implicit character‑set conversion was the root cause.

Problem Identification

Running EXPLAIN SELECT * FROM oc_order oo JOIN orders_detail od ON oo.order_id = od.order_id; showed that the driver table orders_detail was not using its index.

The first table in the join is the driver table; subsequent tables are driven tables that receive parameters from the driver.

Step 1: Check Execution Plan

Use EXPLAIN on the original query to see which tables are scanning without indexes.

Step 2: Inspect Indexes of the Driven Table

Run SHOW INDEX FROM test.orders_detail; to verify the existing indexes.

Step 3: Examine Column Character Sets

Query the information schema to get the character set and collation of the involved columns:

SELECT COLUMN_NAME, character_set_name, collation_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'oc_order' AND COLUMN_NAME = 'order_id';

SELECT COLUMN_NAME, character_set_name, collation_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'orders_detail' AND COLUMN_NAME = 'order_id';

The results reveal a mismatch: one side uses utf8 while the other uses utf8mb4 , causing an implicit conversion.

MySQL Character Set Note

utf8mb4 is a superset of utf8 ; when compared, utf8 values are automatically converted to utf8mb4 , which can prevent index usage.

Verification

Modify the join condition to force the driver column to utf8 :

SELECT *
FROM oc_order oo
JOIN orders_detail od ON CONVERT(oo.order_id USING UTF8) = od.order_id;

Running EXPLAIN again confirms that the index is now used.

Solution

Adjust the SQL Statement

SELECT *
FROM oc_order oo
JOIN orders_detail od ON CONVERT(oo.order_id USING UTF8) = od.order_id;

Align Character Sets

Ensure that both tables use the same character set (preferably utf8mb4 ) to avoid implicit conversion.

Summary

Three common reasons why MySQL may ignore an index:

Applying functions to indexed columns.

Implicit type conversion, such as comparing strings with numbers.

Implicit character‑set conversion, e.g., utf8 being converted to utf8mb4 .