Remember to step on the 'pit' of MySQL in subquery

Preface

MySQL is a commonly used database in projects, in which in query is also very commonly used. During the recent debugging of the project, I encountered an unexpected select query, which actually took 33 seconds!

1. Table structure

1. userinfo table

2. article table

select*fromuserinfowhereidin(selectauthor_idfromartilcewheretype=1);

When you first see the above SQL, you may think that it is a very simple subquery. First find out the author_id, and then use in to query it.

If there is a related index, it will be very fast. In terms of disassembly, it is as follows:

1.selectauthor_idfromartilcewheretype=1;　　2.select*fromuserinfowhereidin(1,2,3);

But the fact is this:

mysql> select count(*) from userinfo;

mysql> select count(*) from article;

mysql> select id,username from userinfo where id in (select author_id from article where type = 1);

33 seconds! Why is it so slow?

3. Cause of the problem

Official document explanation: The in clause is sometimes converted to exists when querying, and is traversed record by record (existing in version 5.5, optimized in 5.6).

refer to:

https://dev.mysql.com/doc/refman/5.5/en/subquery-optimization.html

4. Solution (version 5.5)

1. Use temporary table

select id,username from userinfo

where id in (select author_id from

(select author_id from article where type = 1) as tb);

2. Use join

select a.id,a.username from userinfo a, article b

where a.id = b.author_id and b.type = 1;

5. Supplement

Version 5.6 has been optimized for subqueries in the same way as the temporary table in [4]. Please refer to the official documentation:

If materialization is not used, the optimizer sometimes rewrites a noncorrelated subquery as a correlated subquery.

For example, the following IN subquery is noncorrelated (where_condition involves only columns from t2 and not t1):

select * from t1

where t1.a in (select t2.b from t2 where where_condition);

The optimizer might rewrite this as an EXISTS correlated subquery:

select * from t1

where exists (select t2.b from t2 where where_condition and t1.a=t2.b);

Subquery materialization using a temporary table avoids such rewrites and makes it possible to execute the subquery only once rather than once per row of the outer query.

https://dev.mysql.com/doc/refman/5.6/en/subquery-materialization.html

The article comes from the WeChat public account: HULK front-line technical talks

The above is the detailed content of Remember to step on the 'pit' of MySQL in subquery. For more information, please follow other related articles on the PHP Chinese website!