Impact of Quoting String Primary Keys on MySQL Query Performance

In everyday development we sometimes encounter unexpected performance issues; a colleague pointed out a huge difference when adding single quotes around a string primary key in MySQL queries, which is highly relevant for MySQL performance optimization.

Although using a VARCHAR column as a primary key is generally discouraged, it can still be useful in some scenarios.

The problem appears when the query compares the key value with and without single quotes: the execution time differs by two orders of magnitude.

The test table was created as follows:

CREATE TABLE `foo` (
    `key` VARCHAR(10) NOT NULL,
    `time` INT(11) NOT NULL,
    PRIMARY KEY (`key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

After inserting more than 300,000 rows, the following SELECT statements were executed: SELECT * FROM `foo` WHERE `key` = 1293322797; This query took 0.1288 seconds . SELECT * FROM `foo` WHERE `key` = '1293322797'; With the value quoted, the query took only 0.0009 seconds , roughly a 100‑fold speed‑up.

Running EXPLAIN on both statements revealed that the unquoted version performed a full table scan and did not use the primary index, while the quoted version used the index.

Similar results were observed for range queries:

SELECT * FROM `foo` WHERE `key` > 1293322797;

SELECT * FROM `foo` WHERE `key` > '1293322797';

Again, the quoted query used the index and was much faster.

When the key column was changed to an INT type, the presence or absence of quotes no longer affected performance; both queries used the primary index, and the key_len became shorter.

Below are the EXPLAIN screenshots illustrating the difference:

Without single quotes

With single quotes

These images clearly show that the unquoted query does not use the primary index and performs a full scan, whereas the quoted query leverages the index.

For further reading, click the link below to view the original article: