Does a single quote cause MySQL performance loss?

MYSQL performance loss caused by a single quote

It is inevitable to encounter some dissatisfaction in life, some of which come from ourselves, and some of which do not. Today is the winter solstice, which is said to be the shortest day and longest night in the Northern Hemisphere. Today we are going to share that one of my colleagues mentioned the huge difference between adding single quotes or not, which is very meaningful for MYSQL performance optimization.

As we just said, there will inevitably be some dissatisfaction in life. For example, we use a string type field as the primary key. On the surface, this is too unsatisfactory. However, it has also been proven to be useful. of. The problem arises. When single quotes are added to the field value in the query statement, the query time differs by a hundred times!

The test table I created looks like this:

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

Then insert more than 300,000 pieces of data, and then execute the following SQL statement:

SELECT *
FROM `foo`
WHERE `key` =1293322797

The query takes 0.1288 seconds, It takes about this long, and then, add single quotes to 1293322797:

SELECT *
FROM `foo`
WHERE `key` ='1293322797'

The query takes 0.0009 seconds, which is basically a 100-fold difference! ! ! In other words, without adding single quotes, the performance of MYSQL is reduced by 100 times, which is a shocking ratio!

Later, I used EXPLAIN to run the above two statements respectively, as shown in the two pictures below:

When there are no single quotes

With When using single quotes

#It is obvious that the main index is not used without using single quotes, and a full table scan is performed. Using single quotes can use the upper index.

Later, I tested them separately using greater than, and the returned result sets were the same, and their time-consuming was the same as above. I used EXPLAIN to test, and it was the same as above.

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

With single quotes and without Adding single quotes makes such a big difference! It will have such a big impact on mysql performance.

Later, I changed the field key to the INT type. At this time, there is no difference whether to add single quotes or not. EXPLAIN shows that they can also use the main index, but key_len becomes shorter.

Recommended: "mysql tutorial"

The above is the detailed content of Does a single quote cause MySQL performance loss?. For more information, please follow other related articles on the PHP Chinese website!