MySQL row sub-query optimization

1. What is a row subquery:

Select t1.*, (select vn from T2 where t2.c.1=t1.c3 limit 1) where t1.cn= ' xxx ' ....

Similar to each row through a subquery to query the results obtained, are the row subquery.

2. Case:

MySQL version : Mysql-5.6.16-log

Original sql:

# query_time: 20.769287  lock_time: 0.000152 rows_sent: 10  rows_ examined: 11665408set timestamp=1420020764; Select f3.id,f3.pin,f3.touser,f3.content,f3.created_time,f3.modified_time,f3.imgurl,f3.clientversion, f3.deviceinfo,case when f3.created_time>f3.reply_time or f3.reply_time is  null then  ' 0 '  ELSE  ' 1 '  END as reply_statusfrom  (select f1.id, F1.pin,f1.touser,f1.content,f1.created_time,f1.modified_time,f1.imgurl,f1.clientversion,f1.deviceinfo, (select  f2.created_time from feedback_xx f2 where pin= ' SYSTEM '  and f2.toUser= f1.pin order by f2.created_time desc limit 0,1)  as  reply_ Timefrom feedback_xx f1 group by f1.pin desc order by f1.created_ TIME DESC) f3 where 1=1 limit 0,10;

The query results in more than 20 seconds because each row of the façade is given the result by a subquery. A row subquery exists:

Select F2.created_time from Feedback_info f2 where pin= ' SYSTEM ' and f2.touser=f1.pin order by f2.created_time desc LIMIT 0 , 1;

The SQL is rewritten by the left join method to reduce the number of scans in the inner table.

Optimized to:

SELECT F3.id,f3.pin,f3.touser,f3.content,f3.created_time,f3.modified_time,f3.imgurl,f3.clientversion, F3.deviceinfo,case when F3.created_time>f3.reply_time or f3.reply_time are NULL then ' 0 ' ELSE ' 1 ' END as Reply_status fr Om (select F1.id,f1.pin,f1.touser,f1.content,f1.created_time,f1.modified_time,f1.imgurl,f1.clientversion, F1.deviceinfo,f2.created_time as Reply_time from Feedback_xx F1 left join (select A.touser,a.created_time from Feedback_ XX A where a.pin= ' SYSTEM ' ORDER by a.created_time Desc] f2on f1.pin=f2.tousergroup by f1.pin desc ORDER by F1.created_time DESC) F3 where 1=1;

5274 rows in Set (0.17 sec)

execution plan:

+----+-------------+------------+------+---------------+-------------+---------+----------------+-------+------ ----------------------------------------------+| id | select_type | table       | type | possible_keys | key          | key_len | ref             | rows  | Extra                                                 |+----+-------------+------------+------+---------------+-------------+---------+----------------+------- +----------------------------------------------------+|  1 | primary      | <derived2> | all  | null           | null        | null    | null            | 98773 | NULL                                                  | |   2 | DERIVED     | f1          | all  | idx_pin       | null         | NULL    | NULL            |  9846 | using temporary; using filesort                     | |   2 | DERIVED     | <derived3> | ref   | <auto_key0>   | <auto_key0> | 194      | jrlicai.f1.pin |    10 | NULL                                                  | |   3 | DERIVED     | a           | ref  | idx_pin       |  idx_pin     | 194     | const           |  2207 | Using index condition; Using where; Using  filesort |+----+-------------+------------+------+---------------+-------------+---------+--------------- -+-------+----------------------------------------------------+

Summary: do not use row subquery in the statistical query, the efficiency is very low, must rewrite to join the way.

Attached: 2014 Last blog, next year to continue ...

This article is from the My DBA life blog, so be sure to keep this source http://huanghualiang.blog.51cto.com/6782683/1598203

MySQL row sub-query optimization