The Where condition in MySQL a single quote-induced performance loss

Source: Internet
Author: User

There may be some small details in the daily write SQL that cause the overall SQL performance to degrade several times or even dozens of times times, hundreds of times times. The following example is the performance loss caused by a single quotation mark (' ') in a MySQL statement, which I believe many friends have encountered and even write about.

Let's look at my table structure first:

CREATE TABLE ' D_sku ' (' id ' varchar ( $) not NULL, ' commodity_id ' varchar ( $) DEFAULT NULL, ' counts 'int( One) DEFAULT NULL, ' price 'Double( the,2) DEFAULT NULL, ' status 'int( One) DEFAULT NULL, ' location ' varchar ( -default NULL, ' Create_time ' datetime DEFAULT null, ' create_id ' varchar ( $default NULL, ' Modify_time ' datetime DEFAULT null, ' provalue_str ' varchar ( -) DEFAULT NULL, ' category_id ' varchar ( $) DEFAULT NULL, ' customer_id ' varchar ( $) DEFAULT NULL, ' cert_no ' varchar ( -) DEFAULT NULL, ' profit 'DoubleDEFAULT NULL, ' Check_cargo 'int( One) DEFAULT'0', ' check_time ' datetime DEFAULT NULL, ' keep_last_checked 'int( One) DEFAULT NULL, ' Approval_status 'int( One) DEFAULT'0', ' Code ' varchar ( -) DEFAULT NULL, PRIMARY key (' ID '), key ' Index_price ' (' price ') USING BTREE, key ' Index_category_status ' (' Category _id ', ' status ') using BTREE, key ' Index_modifytime ' (' modify_time ') using BTREE, key ' Index_customerid_categoryid ' (' Cust omer_id ', ' category_id ') using BTREE, key ' Index_certno_customerid ' (' cert_no ', ' customer_id ') using BTREE, key ' INDEX_PR Ovaluestr ' (' Provalue_str ' ( the) , USING BTREE) ENGINE=innodb DEFAULT Charset=utf8 row_format=dynamic

A SKU database table structure pattern for an e-commerce platform, with data bars in the table of 376138. The following two query methods to see the efficiency of execution. Query statements are queries from this table that have data classified as d2a17030-149d-11e5-a9de-000c29d7a3a0 and numbered 5186354366.

1. Example Test

1. Add single quotation marks to the contents of the query

where category_id='d2a17030-149d-11e5-a9de-000c29d7a3a0' and  d.cert_no= ' 5186354366 ';

"Message": Execution succeeded, current return: [1] line, time: [1ms.]. Queries are very fast.

2. Do not add single quotation marks to query content

where category_id='d2a17030-149d-11e5-a9de-000c29d7a3a0' and  d.cert_no= 5186354366;

"Message": Execution succeeded, current return: [1] line, time: [1210ms.] It is clear that the efficiency of the implementation is obvious.

2. The difference between the two analysis

such a query effect is really obvious , add a single quotation mark query 1ms, not add single quote query time is 1210ms. A piece of data is so obvious. It is conceivable how much this performance loss. But why is that? Look at the analysis index first. Use the keyword "explain" To view SQL execution efficiency in detail (keywords using the introductory point portal).

Explain  where category_id='d2a17030-149d-11e5-a9de-000c29d7a3a0' and  d.cert_no='5186354366'; explain  where category_id=  'd2a17030-149d-11e5-a9de-000c29d7a3a0' and  d.cert_no=5186354366;

Figure One: Add single quotation marks

Figure II: single quotes not added

Two Data comparison analysis:

Figure One:

Performance details after adding single quotes, where the table header key is shown here to really use the composite index "Index_certno_customerid", where the columns correspond to "category_id" and "Cert_no". Look at rows again, this means that the query is retrieving only one piece of data, because the index is here, so the data is queried directly by the "Cert_no" number.

Figure II:

Performance details after not adding single quotes, found that the really used index only "Index_category_status", back to the creation of the table structure can be found when the index information is added ' category_id ', ' status ' of the two columns, Indicates that only the category_id is used, and the Cert_no column of the second condition is not indexed, so the loss of performance occurs here.

Summarize:

The reason is because when we create the table structure, the Cert_no field is a varchar type, and where when the single quote is not added when the parameter is used as a numeric type, the different types of queries must be transformed, the data type conversion can not use the index properly. So you can get a conclusion that data of type int is not indexed if it is converted to varchar. We can modify the table structure to change the cert_no to int type, and the performance is normal when using a parameter query that does not add single quotes. It is also possible to add single quotes.

The Where condition in MySQL a single quote-induced performance loss

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.