MySQL does not support subquery optimization.

MySQL does not support subquery optimization.

1. 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 );
Insert into tt2 values (1, 2), (2, 2 );

2. Execution Plan and Problems

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 | 4 | 100.00 | Using join buffer (Block Nested Loop) |
| 2 | SUBQUERY | tt2 | ALL | NULL | 1 | 100.00 | Using where |
+ ---- + ------------- + ------- + ------------- + --------- + ------ + ---------- + --------------------------------------- +
3 rows in set, 1 warning (0.00 sec)
How is the execution sequence of this statement executed? Is it executed from top to bottom according to the execution plan?

3. Analysis

--- The two whose IDs are 1 are tt1 and tt2 in the From clause. Use the nested loop connection algorithm to establish an inner join. tt2 is the external table, so tt2 is started first; tt1 is started later.
--- If the value of id is 2, The subquery in the target column is executed in the table in the FROM clause. The value of the target column is required to be sent to the client after the connection.
--- Theoretically, it can be optimized. The method is: Only one column in the target column is the same as the WHERE condition. Therefore, we can infer that the subquery result is 2.
--- However, MySQL does not optimize such subqueries.
--- In addition, if multiple rows are returned for the Subquery, MySQL reports an ERROR: ERROR 1242 (21000): Subquery returns more than 1 row.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: