MySQL Index Failure When VARCHAR Condition Lacks Quotes

This article shares a practical MySQL indexing issue encountered at work, where a VARCHAR column query without surrounding quotes causes the index to become ineffective.

Phenomenon description : The problem occurs because MySQL performs an implicit type conversion when the query value is not quoted, resulting in a full table scan instead of using the index.

Example table definition:

CREATE TABLE `order_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` varchar(32) DEFAULT '',
  `name` varchar(11) DEFAULT '',
  `age` tinyint(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

Two queries were executed:

explain SELECT * from order_test t where t.`user_id` = '11223344';

This query uses the index as shown in the resulting explain plan image.

In contrast, the unquoted version:

explain SELECT * from order_test t where t.`user_id` = 11223344;

produces a full table scan, confirming that the index is not used.

Reproduction scenario : In a MyBatis XML mapper, using ${user_id} injects the value without quotes:

<select id="selectByMap" resultMap="BaseResultMap" parameterType="java.util.Map">
    select * from order_test where user_id = ${user_id}
</select>

Corresponding Java code:

public void test2() {
    Map<String, Object> map = new HashMap<>();
    map.put("user_id", 11223344);
    mapper.selectByMap(map);
}

The generated SQL is:

select * from order_test where user_id = 11223344

Because the value is not quoted, MySQL’s optimizer performs an implicit conversion, causing the index on user_id to be ignored.

Reason for index loss : MySQL’s optimizer decides not to use an index when a function (such as an implicit CAST) is applied to the indexed column, as this can break the ordered nature of the index values.

Summary :

String‑type indexed queries must include single quotes; otherwise MySQL will not use the index.

MySQL does not support functional indexes, so avoid adding functions to query conditions.