Shulou(Shulou.com)05/31 Report--

Editor to share with you a sample analysis of the order of SELECT execution in MySQL, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

The complete syntax of the SELECT statement is:

(7) SELECT (8) DISTINCT (1) FROM (3) JOIN (2) ON (4) WHERE (5) GROUP BY (6) HAVING (9) ORDER BY (10) LIMIT

Description: the sequence number in front of the syntax is SELECT execution order

The SELECT execution order of MySQL is divided into 10 steps, as noted above, the first is the FROM operation, and the last is the LIMIT operation. Each of these operations produces a virtual table, which serves as an input for processing, except that these virtual tables are transparent to the user, but only the last virtual table is returned as a result. If a clause is not specified in the statement, the corresponding step is skipped.

Let's analyze each stage of query processing in detail.

SELECT a.customer_id, COUNT (b.order_id) as total_orders FROM table1 AS a LEFT JOIN table2 AS b ON a.customer_id = b.customer_id WHERE a.city = 'hangzhou' GROUP BY a.customer_id HAVING count (b.order_id) < 2 ORDER BY total_orders DESC

1. FORM: calculates the Cartesian product of the table on the left and the table on the right of FROM to generate a virtual table VT1.

2. ON: perform ON filtering on the virtual table VT1, and only those rows that match will be recorded in the virtual table VT2.

3. JOIN: if OUTER JOIN is specified (such as left join, right join), the unmatched rows in the reserved table will be added to the virtual table VT2 as external rows, resulting in the virtual table VT3.

4. WHERE: perform WHERE conditional filtering on the virtual table VT3. Only matching records are inserted into the virtual table VT4.

5. GROUP BY: according to the columns in the group by clause, the records in VT4 are grouped to generate VT5.

6. HAVING: apply having filtering to the virtual table VT5, and only the matching records will be inserted into the virtual table VT6.

7. SELECT: perform the select operation, select the specified column, and insert it into the virtual table VT7.

8. DISTINCT: deduplicates the records in VT7. Generate virtual table VT8.

9. ORDER BY: sort the records in the virtual table VT8 to generate the virtual table VT9.

10. LIMIT: fetch the record of the specified row, generate the virtual table VT10, and return the result.

The above is all the contents of the article "sample Analysis of SELECT execution order in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.