Slow MySql in subquery Efficiency Optimization

Currently, CMS, blog, BBS, and so on all like to use tag tags for cross-linking, so I also have a taste of it. However, I found thatArticleThe list is slow. It takes 5 seconds! I was puzzled (and finally solved it). My table structure is as follows, and there are only 690 articles.

Article table article (ID, title, content)
Tag table tag (TID, tag_name)
Article_tag (ID, tag_id, article_id)
Here, the TID of a tag is 135. I can query the list of articles whose TID is 135.
I used the following statements to find that the speed is slow. I only wrote 690 articles.
Select ID, title from article where ID in (
Select article_id from article_tag where tag_id = 135
)
The speed is fast: Select article_id from article_tag where tag_id = 135
The query result is five articles with the ID 428,429,430,431,432
I used the following SQL statement to check the article quickly.
Select ID, title from article where ID in (
428,429,430,431,432
)
I don't seem to be so slow in sqlserver. I don't know how to write MySQL well, and I don't know where it is.

Then I found the solution:

Select ID, title from article where ID in (
Select article_id from (select article_id from article_tag where tag_id = 135) as TBT
)

 

 

Other solutions: (example)

Mysql>Select * From abc_number_prop where number_id in (select number_id from abc_number_phone where phone = '2016 ');

The output content is omitted to save space.

67 rows in SET (12.00 Sec)

It took only 67 rows of data to return, but it took 12 seconds. There may be many such queries in the system at the same time, and the system will surely be unable to handle them. Use DESC to check the description (Note: The description can also be used)

mysql> DESC select * From abc_number_prop where number_id in (select number_id from abc_number_phone where phone = '2016 ');
+ ---- + -------------------- + ------------------ + -------- + ----------------- + ------- + --------- + ------------ + --------- + signature +
| ID | select_type | table | Type possible_keys | key | key_len | ref | rows | extra |
+ ---- + keys + ------------------ + -------- + --------------- + ------- + --------- + ------------ + --------- + keys +
1 | primary | abc_number_prop | all | null | 2679838 | using where |
| 2 | dependent subquery | abc_number_phone | eq_ref | phone, number_id | phone | 70 | const, func | 1 | using where; using index |
+ ---- + -------------------- + ------------------ + -------- + ----------------- + ------- + --------- + ------------ + --------- + rows +
2 rows in SET (0.00 Sec)

From the above information, we can see that more than 2 million rows will be scanned when this query is executed. Isn't the index created? Let's take a look.

Mysql> Show index from abc_number_phone;
+ ------------------ + ------------ + ------------- + -------------- + ----------------- + ----------- + ------------- + ---------- + -------- + -------------- + ------------- +
| Table | non_unique | key_name | seq_in_index | column_name | collation | cardinality | sub_part | packed | null | index_type | comment | index_comment |
+ ------------------ + ------------ + ------------- + -------------- + ----------------- + ----------- + ------------- + ---------- + -------- + -------------- + ------------- +
| Abc_number_phone | 0 | primary | 1 | number_phone_id | A | 36879 | null | btree |
| Abc_number_phone | 0 | phone | 1 | phone | A | 36879 | null | btree |
| Abc_number_phone | 0 | phone | 2 | number_id | A | 36879 | null | btree |
| Abc_number_phone | 1 | number_id | 1 | number_id | A | 36879 | null | btree |
| Abc_number_phone | 1 | created_by | 1 | created_by | A | 36879 | null | btree |
| Abc_number_phone | 1 | modified_by | 1 | modified_by | A | 36879 | null | Yes | btree |
+ ------------------ + ------------ + ------------- + -------------- + ----------------- + ----------- + ------------- + ---------- + -------- + -------------- + ------------- +
6 rows in SET (0.06 Sec)

Mysql> Show index from abc_number_prop;
+ ----------------- + ------------ + ------------- + -------------- + ---------------- + ----------- + ------------- + ---------- + -------- + -------------- + ----------- + -----------------
| Table | non_unique | key_name | seq_in_index | column_name | collation | cardinality | sub_part | packed | null | index_type | comment | index_comment |
+ ----------------- + ------------ + ------------- + -------------- + ---------------- + ----------- + ------------- + ---------- + -------- + -------------- + ----------- + -----------------
| Abc_number_prop | 0 | primary | 1 | number_prop_id | A | 311268 | null | btree |
| Abc_number_prop | 1 | number_id | 1 | number_id | A | 311268 | null | btree |
| Abc_number_prop | 1 | created_by | 1 | created_by | A | 311268 | null | btree |
| Abc_number_prop | 1 | modified_by | 1 | modified_by | A | 311268 | null | Yes | btree |
+ ----------------- + ------------ + ------------- + -------------- + ---------------- + ----------- + ------------- + ---------- + -------- + -------------- + ----------- + -----------------
4 rows in SET (0.15 Sec)

From the above output, we can see that the two tables have created indexes on the number_id field.

Check whether the subquery has any problems.

Mysql>Desc select number_id from abc_number_phone where phone = '000000 ';
+ ---- + ------------- + ------------------ + ------ + --------------- + ------- + --------- + ------- + ------ + ---------------------------- +
| ID | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
+ ---- + ------------- + ------------------ + ------ + --------------- + ------- + --------- + ------- + ------ + ---------------------------- +
| 1 | simple | abc_number_phone | ref | phone | 66 | const |6| Using where; Using index |
+ ---- + ------------- + ------------------ + ------ + --------------- + ------- + --------- + ------- + ------ + ---------------------------- +
1 row in SET (0.00 Sec)

No problem. You only need to scan several rows of data. The index takes effect. Query and view

Mysql>Select number_id from abc_number_phone where phone = '201312 ';
+ ----------- +
| Number_id |
+ ----------- +
| 1, 8585 |
| 1, 10720 |
| 1, 148644 |
| 1, 151307 |
| 1, 170691 |
| 1, 221897 |
+ ----------- +
6 rows in SET (0.00 Sec)

Directly store the data obtained from the subquery in the preceding query.

Mysql>Select * From abc_number_prop where number_id in (8585,107 20, 148644,151 307, 170691,221 897 );

67 rows in SET (0.03 Sec)

It seems that MySQL is not good enough to process subqueries. I tried both MySQL 5.1.42 and MySQL 5.5.19.

I searched the network and found that many people have encountered this problem:

Reference 1: Use join instead of sub-query MySQL optimization series records
Http://blog.csdn.net/hongsejiaozhu/article/details/1876181
Reference 2: website development diary (14)-mysql subquery and nested Query Optimization
Http://dodomail.iteye.com/blog/250199

Based on the suggestions of these materials on the Internet, use join instead.

Before modification: Select * From abc_number_prop where number_id in (select number_id from abc_number_phone where phone = '2016 ');

After modification: select a. * From abc_number_prop a inner join abc_number_phone B on A. number_id = B. number_id where phone = '123 ';

Mysql>Select a. * From abc_number_prop a inner join abc_number_phone B on A. number_id = B. number_id where phone = '2016 ';

67 rows in SET (0.00 Sec)

Good results. The query time is almost 0. Let's see how MySQL executes this query.

Mysql>Desc select a. * From abc_number_prop a inner join abc_number_phone B on A. number_id = B. number_id where phone = '123 ';
+ ---- + ------------- + ------- + ------ + ----------------- + ----------- + --------- + ------------------- + ------ + ------------------------ +
| ID | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
+ ---- + ------------- + ------- + ------ + ----------------- + ----------- + --------- + ------------------- + ------ + ------------------------ +
| 1 | simple | B | ref | phone, number_id | phone | 66 | const |6| Using where; Using index |
| 1 | simple | A | ref | number_id | 4 | EAP. B. number_id |3|
+ ---- + ------------- + ------- + ------ + ----------------- + ----------- + --------- + ------------------- + ------ + ------------------------ +
2 rows in SET (0.00 Sec)

Conclusion: When the subquery speed is slow, you can use join to rewrite the subquery for optimization.

There are also articles on the Internet that queries using join statements are not necessarily faster than statements using subqueries.

Reference 3: changed my views on MySQL subqueries
Http://hi.baidu.com/yzx110/blog/item/e694f536f92075360b55a92b.html

 

 

The MySQL Manual also mentioned that the specific original article is in this chapter of the MySQL document:

I .3. restrictions on subqueries

13.2.8. subquery syntax

Excerpt:

1) subqueries using in:

Subquery Optimization for in is not as valid tive as for the = operator or for in (Value_list) Constructs.

A typical case for poor in subquery performance is when the subquery returns a small number of rows but the outer query returns a large number of rows to be compared to the subquery result.

The problem is that, for a statement that uses an in subquery, the optimizer rewrites it as a correlated subquery. Consider the following statement that uses an uncorrelated subquery:

 
Select... from T1 where t1.a in (select B from T2 );
 

The optimizer rewrites the statement to a correlated subquery:

 
Select... from T1 where exists (select 1 from T2 where t2. B = t1.a );
 

If the inner and outer queries returnMAndNRows, respectively, the execution time becomes on the order of O (M×N), Rather than O (M+N) As it wocould be for an uncorrelated subquery.

An implication is that an in subquery can be much slower than a query written using an in (Value_list) Construct that lists the same values that the subquery wocould return.

2) about converting subqueries into join:

The optimizer is more mature for joins than for subqueries, so in your cases a statement that uses a subquery can be executed more efficiently if you rewrite it as a join.

An exception occurs for the case where an in subquery can be rewritten as a select distinct join. Example:

 
Select Col from T1 where id_col in (select id_col2 from T2 whereCondition);
 

That statement can be rewritten as follows:

 
Select distinct Col from T1, T2 where t1.id _ Col = t2.id _ Col andCondition;
 

But in this case, the join requires an extra distinct operation and is not more efficient than the subquery