Implicit Conversion and Collation Mismatch
A customer database has 5 million rows: CREATE TABLE customers (id INT PRIMARY KEY, phone VARCHAR(20), email VARCHAR(255), created_at DATETIME, KEY idx_phone (phone), KEY idx_email (email)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;. A legacy table legacy_contacts stores email VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci. Three queries are slow. Query A, SELECT id FROM customers WHERE phone = 5551234;, scans all rows even though idx_phone exists. Query B joins customers c JOIN legacy_contacts l ON c.email = l.email and also scans. Query C, SELECT id FROM customers WHERE DATE(created_at) = '2024-05-01';, scans all rows; there is no index on created_at yet. The application uses prepared statements and binds parameters, but the phone parameter is currently bound as an integer.
1. Diagnose Query A and show the fix at both SQL and application-binding levels.
Read the worked answer
phone is a string column, but 5551234 is an integer literal. MySQL compares a string and a number by converting the string to a number, so the predicate becomes a function of the column rather than a lookup of an indexed value. The optimizer cannot use idx_phone; EXPLAIN shows a full scan, and SHOW WARNINGS may report that the column is being converted to DOUBLE.
Fix the SQL by quoting the literal:
SELECT id FROM customers WHERE phone = '5551234';
Fix the application bind as well. With a prepared statement, bind the phone parameter as a string, not an integer:
SELECT id FROM customers WHERE phone = ?;
Binding '5551234' keeps the comparison in the string domain. Also normalize phone values consistently: leading zeros, country codes, spaces, and punctuation should be handled by the application or a canonical stored format. A phone number is an identifier, not a numeric quantity, so VARCHAR is the right type.
2. Diagnose Query B and explain why putting `COLLATE` inside the `ON` clause is usually not the best fix.
Read the worked answer
The join compares utf8mb4_0900_ai_ci on customers.email with utf8mb3_general_ci on legacy_contacts.email. The character sets and collations differ, so MySQL must convert one side to a common collation for each comparison. That conversion can prevent use of the index on the converted column; EXPLAIN may show a full scan, a join buffer, or an index only on one side.
Putting COLLATE in the join condition, such as ON c.email = l.email COLLATE utf8mb4_0900_ai_ci, forces an expression on the column and can still block index use. It also leaves the schemas inconsistent, so every future query must repeat the workaround.
The durable fix is to align the schemas. After checking for characters that cannot be represented and for duplicate or case-insensitive collisions, run:
ALTER TABLE legacy_contacts
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci;
Then rebuild or verify the email index. If the legacy table cannot be changed immediately, a generated normalized column with a matching index on each side is a transitional option, but it adds complexity and must be maintained.
3. Diagnose Query C and rewrite it so an index on `created_at` can be used.
Read the worked answer
DATE(created_at) applies a function to the indexed column, so MySQL must evaluate DATE() for every row before comparing. This is a non-sargable predicate; even after adding KEY idx_created (created_at), the optimizer cannot use an index range on the raw column.
Rewrite the predicate as a half-open range:
SELECT id
FROM customers
WHERE created_at >= '2024-05-01'
AND created_at < '2024-05-02';
Add the index:
ALTER TABLE customers ADD INDEX idx_created (created_at);
The half-open range includes all DATETIME values on May 1, including fractional seconds, and excludes midnight at the start of May 2. It allows a range scan on idx_created. The same principle applies to YEAR(), MONTH(), and DATE_FORMAT() filters: rewrite them as ranges on the raw column whenever possible.