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

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