Home > Database > Mysql Tutorial > Does a single quote cause MySQL performance loss?

Does a single quote cause MySQL performance loss?

藏色散人
Release: 2020-05-18 16:31:28
forward
2398 people have browsed it

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;
Copy after login

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

SELECT *
FROM `foo`
WHERE `key` =1293322797
Copy after login

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

SELECT *
FROM `foo`
WHERE `key` ='1293322797'
Copy after login

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

Does a single quote cause MySQL performance loss?

With When using single quotes

Does a single quote cause MySQL performance loss?

#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'
Copy after login

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.

Does a single quote cause MySQL performance loss?

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!

Related labels:
source:learnku.com
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template