MySQL does not support sub-query optimization One example

Create a table

CREATE TABLE TT1 (ID int primary KEY, C1 int);
CREATE TABLE TT2 (ID int primary KEY, C2 int);
INSERT into TT1 values (2,2), (3,3), (bis);
INSERT into TT2 values (2,2);

II. implementation plans and issues

mysql> explain EXTENDED Select Tt1.c1, (select Tt2.c2 from Tt2 where c2=10) from Tt1, TT2;
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------- -------------------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Filtered | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------- -------------------------+
| 1 | PRIMARY | TT2 | Index | NULL | PRIMARY | 4 |    NULL |   2 | 100.00 | Using Index |
| 1 | PRIMARY | TT1 | All | NULL | NULL | NULL |    NULL |   4 | 100.00 | Using Join buffer (Block Nested Loop) |
| 2 | subquery | TT2 | All | NULL | NULL | NULL |    NULL |   2 | 100.00 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------- -------------------------+
3 rows in Set, 1 Warning (0.00 sec)
What is the execution order of this statement, which is executed from top to bottom according to the execution plan?

Three-analysis

The---ID value is 1 of two, is the FROM clause in the TT1 and Tt2, with the block nested loop join algorithm to do inner connection, TT2 is the appearance, so the first start is TT2, after the start is TT1.
---an ID value of 2 is a subquery in the destination column, followed by the table in the FROM clause. Occurs after a connection requires the value of the target column to be sent to the client stage.
---can theoretically be optimized by: The target column has only one column and the Where condition, so it can be inferred that the result of this subquery is 2.
---however, MySQL does not optimize such subqueries.
---Also, for subqueries here, if the results return multiple rows, MySQL reports an error: Error 1242 (21000): subquery returns more than 1 row.

This article is from the Linux OPS blog, so be sure to keep this source http://2853725.blog.51cto.com/2843725/1546306

MySQL does not support sub-query optimization One example