How MySQL Implicit Type Conversion Turned an Indexed Query Into a 7M-Row Scan
A missing quote made MySQL scan 7.2 million rows. Here's how EXPLAIN exposed the problem and what it taught us about safe production updates.
A seemingly routine database maintenance task turned into a major crisis when a small batch of customer account records required a minor state update. Despite the simplicity of the UPDATE statement, MySQL's implicit type conversion caused the database to scan over seven million rows instead of just the few it needed to modify. This issue arose from a mismatch between the string-based provider_code and an unquoted integer literal in the query's filter condition.
The database treated the integer as a double-precision floating-point number and performed an implicit conversion for every row, effectively bypassing the index and triggering a full table scan. To prevent this from happening again, the lesson emphasizes the importance of using quotes when comparing numbers to string columns. However, even after correcting the data types, the database still needed to scan 3.6 million rows due to the composite index's limitations on the date-range filter condition.
Written by urgent.news from HackerNoon's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
