Why a Simple MySQL UPDATE Took 40 Seconds: Charset Conversion Gotcha

Problem Background

A front‑end application experienced extremely slow login times, which DBA analysis linked to high CPU usage caused by a MySQL UPDATE statement. The slow query appeared in the MySQL slow‑log with an average execution time of about 40 seconds.

Question Raised

The author wondered why a simple UPDATE on a varchar column behaved so poorly, whether the issue stemmed from character‑type indexing differences, internal conversions shown in the slow‑log, or a bug in MySQL 5.1 (fixed in later versions).

Comparative Test

To reproduce the problem, the table data was copied to a test environment and a stored procedure was written to execute the same UPDATE. The handler statistics showed a massive Handler_read_next count, indicating a full‑table scan, while executing the same statement directly showed Handler_read_rnd_next = 0, meaning an index scan.

Tracing the execution (MySQL 5.6+ trace feature) revealed that MySQL performed an implicit charset conversion: convert(`push_list_s`.`APNS_PUSH_ID` using utf8) This conversion forced the entire APNS_PUSH_ID column to be transformed before matching, turning the operation into a full‑table scan.

Verification Steps

The verification involved:

Running the stored procedure and observing handler metrics.

Executing the UPDATE directly and confirming index usage.

Enabling trace to see the implicit convert(... using utf8) operation.

After unifying the column’s charset to the table‑level UTF‑8, the same UPDATE completed quickly, confirming that the charset mismatch was the root cause.

Conclusion

The issue was not a bug in the MySQL version used (5.6) but a mismatch between the parameter charset passed to the stored routine and the column charset. The fix is to declare the parameter charset explicitly or alter the column to use UTF‑8, eliminating the costly conversion and restoring normal performance.

MySQL’s own response echoed this: “Problem is that the stored routine does not explicitly declare the charset of the parameter that is passed to the stored routine. It must match the column's charset to which you're comparing it to.”