Shulou(Shulou.com)06/01 Report--

Today, the editor will bring you an article about how a single quote can change the performance of MySQL. The editor thinks it is very practical, so I will share it for you as a reference. Let's follow the editor and have a look.

Use a field of type string as the primary key. The problem also comes out, when the query statement on the value of the field in single quotation marks and no query time difference a hundred times!

The test table I set up 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 execute the following SQL statement:

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

The query took 0.1288 seconds, about that long, and then put 1293322797 in single quotes:

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

Query takes 0.0009 seconds, basically 100 times the difference! In other words, the performance of MYSQL without single quotation marks has been lost 100 times, which is a shocking proportion!

Later, use EXPLAIN to run the above two sentences respectively, see the following two pictures:

When there are no single quotation marks

When there are single quotation marks

Obviously, without using single quotation marks, the primary index is not used, and the whole table is scanned, and the index can be used with single quotation marks.

Later, I tested it with greater than separately, and the result set was the same, and their time-consuming was the same as above, and the EXPLAIN test was the same as above.

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

There is such a big difference between adding single quotation marks and not adding single quotation marks! It will have such a big impact on the performance of mysql.

Later, I changed the field key to INT, and at this point, without single quotation marks, there was no difference. EXPLAIN showed that they could all use the primary index, but the key_len was shorter.

About a single quotation mark change MySQL performance to share here, I hope the above content can be of some help to you, can learn more knowledge. If you like this article, you might as well share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.