Analysis of MySQL performance problems caused by a single quotation mark

For large systems, Oracle and sqlserver are undoubtedly the best choice. You can see that there are more and more small websites. They don't have their own servers, just buy others' space and databases, however, the performance of such a small database is certainly not comparable with that of large databases, but small databases also need to optimize their own methods. Today, we will share with you the huge difference between MySQL and single quotes, it is meaningful for MySQL performance optimization.

As we have just said, there will inevitably be some disappointments in our lives. For example, we use a string-type field as the primary key. On the surface, this is not satisfactory. However, it turns out to be useful. The problem arises. When a single quotation mark is added to the field value in the query statement and the query time difference is times!

Test Table:CopyCodeThe Code is as follows: Create Table 'foo' ('key' varchar (10) Not null, 'time' int (11) not null, primary key ('key ')) engine = MyISAM default charset = utf8;

Insert more than 0.3 million data records and execute the following SQL statement:Copy codeCode: Select * From 'foo' where 'key' = 1293322797

The query takes 0.1288 seconds. It takes about so long. Then, a single quotation mark is added to 1293322797:Copy codeThe Code is as follows: Select * From 'foo' where 'key' = '000000'

Query takes 0.0009 seconds, which is basically 100 times different !!! That is to say, the performance loss of MySQL is 100 times without single quotes, which is a shocking proportion!

Later, I ran the preceding two statements using explain. See the following two figures:

When no single quotes exist

With Single quotes

Obviously, if you do not use single quotes without the primary index and perform a full table scan, you can use single quotes to use the index.
Later, I tested them with a value greater than each other, and the returned result set was the same, and their time consumption was the same as the above. I used an explain test, and they were the same as above.

Copy code The Code is as follows: Select * From 'foo' where 'key'> 1293322797 select * From 'foo' where 'key'> '123'

Adding single quotes and not single quotes are such a big difference! It will have such a big impact on MySQL performance.
After that, I changed the field 'key' to the int type. At this time, there is no difference when the single quotation marks are added. The explicit statement shows that they can also use the primary index, the key_len is shortened.

that's all. To sum up, we still have to put a single quotation mark in writing SQL queries. It seems that there is no harm.