MySQL does not support subquery optimization.

Source: Internet
Author: User

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:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.