MySQL plus single quotes and no single quotes for performance comparisons

Source: Internet
Author: User

The test table I set up is like this:

The code is as follows Copy Code

CREATE TABLE ' foo ' (
' Key ' VARCHAR not NULL,
' Time ' INT (one) is not NULL,
PRIMARY key (' key ')
) Engine=myisam DEFAULT Charset=utf8;


Then insert the 30多万条 data, and then execute the following SQL statement:

The code is as follows Copy Code

SELECT *
From ' foo '
WHERE ' key ' =1293322797

The query takes 0.1288 seconds, takes about a long time, and then, adds a single quote to 1293322797:

The code is as follows Copy Code

SELECT *
From ' foo '
WHERE ' key ' = ' 1293322797 '

The query takes 0.0009 seconds, basically 100 times times the difference!!! In other words, without single quotes MySQL performance loss of 100 times times, very shocking proportions!

Later, explain ran the above two statements, see the following two pictures:


When there are no single quotes


When you have single quotes

Obviously, without using single quotes, the primary index is not used, and a full table scan is performed, using single quotes to use the index.
Later I tested with greater than, and returned the same result set, and their time consuming is the same as above, with the explain test, as well as above

The code is as follows Copy Code

SELECT *
From ' foo '
WHERE ' key ' >1293322797
SELECT *
From ' foo '
WHERE ' key ' > ' 1293322797 '

This is all, to sum up, we write SQL query when the time is still tired of adding single quotes, it seems that there is no harm. Thanks for watching, let's play a message we just received: "I'm going downstairs to buy some dumplings to celebrate the winter solstice!"

To sum up, in MySQL, single quotes are representative characters do not need to be edited, if not the character also requires MySQL to do a type of judgment operation so naturally slow.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.