MySQL Index Ignored? Implicit Type Conversion Causes Full Table Scan

Case Study: Missing Quotes Turn Index Lookup into Full Table Scan

A colleague reported a monitoring page loading in ~20 seconds. The slow query log revealed:

SELECT * FROM cons_load WHERE cons_id = -231231231231234123;

The table cons_load has a VARCHAR(100) column cons_id with an index idx_cons_id. The EXPLAIN plan showed type=ALL and rows=2000000, confirming a full table scan.

Adding quotes around the literal:

SELECT * FROM cons_load WHERE cons_id = '-231231231231234123';

Changed the plan to type=ref, key=idx_cons_id, rows=12. Response time dropped from 19.3 seconds to under 50 milliseconds.

Why It Happens: Implicit Type Conversion

MySQL performs implicit type conversion when comparing a string column to a numeric value. It effectively rewrites the predicate as:

SELECT * FROM cons_load WHERE CAST(cons_id AS SIGNED) = -231231231231234123;

Because a function ( CAST) is applied to the indexed column, the index cannot be used.

Four Other Common Index Invalidation Patterns

1. Applying Functions to Indexed Columns

-- Wrong: function on column
SELECT * FROM cons_load WHERE DATE(collect_time) = '2026-07-25';
-- Correct: range query
SELECT * FROM cons_load WHERE collect_time >= '2026-07-25 00:00:00' AND collect_time < '2026-07-26 00:00:00';

2. Character Set Mismatch Causing Implicit Conversion

If the column is utf8mb4 but the parameter or joined column is utf8, MySQL converts one side, invalidating the index.

3. Collation Mismatch

Different collations (e.g., utf8mb4_unicode_ci vs utf8mb4_general_ci) also trigger conversion and index loss.

4. LIKE Pattern Starting with Wildcard

-- Wrong: leading wildcard
SELECT * FROM cons_load WHERE cons_id LIKE '%231234123%';
-- Correct: trailing wildcard
SELECT * FROM cons_load WHERE cons_id LIKE '231234123%';

Prevention Guidelines

Quote parameters in SQL: Use WHERE cons_id = '-231231231231234123' instead of WHERE cons_id = -231231231231234123.

ORM field mapping: An entity field String consId must receive a String, not a Long or Integer. In MyBatis, specify #{consId, jdbcType=VARCHAR}.

Match join column types and charsets: Join bigint to bigint, varchar to varchar, and keep all character sets as utf8mb4.

Quick Diagnosis Checklist

Check possible_keys in EXPLAIN: if NULL, the index is likely not being considered.

Verify that the WHERE clause field type matches the parameter type (e.g., varchar vs numeric).

Examine the Extra column for Using where and the rows estimate to gauge the scan impact.

Cheat Sheet: Common Scenarios and Fixes

Varchar passed numeric literal: Missing quotes → add quotes.

Function on indexed column: DATE(collect_time) → rewrite as range query.

LIKE with leading wildcard: LIKE '%value%' → LIKE 'value%'.

Charset/collation mismatch: Unify to utf8mb4.

MyBatis parameter type mismatch: Add jdbcType=VARCHAR.

The core lesson: an index is like a book's directory sorted by string order; querying it as a number makes the directory useless. A two-character fix can yield a 300x performance gain.