MySQL (v) SELECT statement Execution order

The previous article describes the order in which Oracle's select syntax is executed, which describes the order in which MySQL's select syntax is executed. MySQL's select syntax is executed in the same order as Oracle, only adding MySQL's unique limit syntax.

Directory

First, the process of the SELECT statement

1. From phase

2. Where phase

3. GROUP by stage

4. Having stage

5. Select Stage

6. ORDER by stage

7. Limit phase

First, the process of the SELECT statement

Query operations are the most frequently used operations in a relational database and are the basis for other SQL statements, such as Delete, UPDATE.

We know that the approximate syntax structure for SQL queries is as follows:

(5)SELECT DISTINCT <Select_list>                     (1) from <Left_table> <Join_type> JOIN <Right_table>  on <On_predicate>(2)WHERE <Where_predicate>(3)GROUP  by <Group_by_specification>(4) having <Having_predicate>(6)ORDER  by <Order_by_list>
(7) LIMIT N, M

The order of query processing is as follows:

1. From
2. On
3. JOIN
4. WHERE
5. GROUP by
6. Having
7. SELECT
8. DISTINCT
9. ORDER by
10. LIMIT

When these steps are executed, each step produces a virtual table that is used as input to the next step. These virtual tables are not available to callers (client applications or external queries). Only the table generated in the last step is returned to the caller. If you do not specify a clause in the query, the corresponding step is skipped.

What each of the select phases did:

1. From phase

The from stage identifies the source table of the query and processes the table operator. In queries that involve join operations (various joins), there are several main steps:

• ask for Cartesian product . Regardless of the type of join operation, the first is to perform a cross join, which is the Cartesian product (Cartesian product), which generates the virtual table vt1-j1.
• On filter . This phase filters the vt1-j1 generated from the previous step, filtering the predicate that appears in the ON clause, and the line that evaluates to TRUE for the predicate is tested and inserted into VT1-J2.
• add an external row . If you specify a OUTER join, such as a left Outerjoin, a right OUTER join, you also need to add a row to vt1-j2 that is not found in the vt1-j2 as an outer row, and a vt1-j3 is generated. If the FROM clause contains more than two tables, repeat the 3 steps for the result table vt1-j3 and the next table that were generated for the previous connection until all the tables have been processed.

After the above steps, the from phase is complete.

2. Where phase

The Where stage filters the rows in the VT1 based on the conditions in the <where_predicate>, allowing the rows to be set to be inserted into the VT2. The data is not grouped at this time, so there is no filtering of the statistics in the Where.

3. GROUP by Stage

The group stage groups the rows in the VT2 according to the specified list of column names, generating VT3. Finally, each group has only one row. in the group by phase, the database considers the two null values to be equal, so the null values are divided into the same grouping.

4. Having stage

This stage filters the grouping of VT3 based on the predicates that appear in the HAVING clause and inserts the eligible groups into the VT4. count (expr) returns the number of rows that are not null for expr, and count (1), COUNT (*) returns all quantities, including null values.

5. Select Stage

This stage is the projection process that processes the elements mentioned in the SELECT clause, producing VT5. This step is generally done in the following order:

• Computes an expression in the select list, generating a vt5-1.
• If you have distinct, delete the duplicate rows in the vt5-1 and generate the vt5-2.

6. ORDER by stage

Sorts the rows in vt5-2 according to the list of columns specified in the ORDER BY clause, generating VT6. If you do not specify a sort, the data is not always sorted in the primary key order. Null is considered the minimum value.

7. Limit phase

Takes a record of the specified row, generates a virtual table VT7, and returns it to the queried user. limit N, the efficiency of M is very low, you can generally optimize the where ID >? limit 10 by specifying a range in the Where condition.

MySQL (v) SELECT statement Execution order