Execution sequence of select statements

The execution sequence of select is a commodity table tp_goods:
Mysql> select * from tp_goods;
+ ---- + ----------------------- + ------------- + -------------- + -------- +
| Id | goods_name | goods_price | market_price | cat_id |
+ ---- + ----------------------- + ------------- + -------------- + -------- +
| 1 | hammer mobile phone t1 White 32G | 1998.00 | 2488.00 | 1 |
| 2 | Lenovo E431 notebook | 4029.00 | 4998.50 | 2 |
| 3 | mysql database introduction | 29.80 | 36.30 | 4 |
| 4 | Apple 6 plus Singapore | 2998.00 | 6299.00 | 1 |
| 5 | OSA women's down jacket 2014 new | 538.50 | 1510.00 | 3 |
+ ---- + ----------------------- + ------------- + -------------- + -------- +

Based on online materials
Mysql> select cat_id, goods_name, goods_price from tp_goods where cat_id = 1;
First, execute the from operation to obtain the virtual table VT1 ???> Then, execute the where operation to filter all rows whose cat_id is 1 and obtain the virtual table VT2. In this case, is the column still all columns in the tp_goods table?> Finally, execute the select operation. the column is cat_id, goods_name, and goods_price. The result is returned.
-----------------------------------------------------------------------
Mysql> select cat_id, max (goods_price) as max_price from tp_goods group by cat_id;
Problem:
1. how is this statement executed? are group by and max functions executed at the same time?
2. Who will execute group by and select first?

Reply to discussion (solution)

1. group? First
2. group? First

Where> group by> aggregate function? > Select

1. group? First
2. group? First

Where> group by> aggregate function? > Select

What is the virtual table generated after the execution of group?
What are the rows and columns?

The execution sequence you understand is incorrect, at least not scientific.
If tp_goods has 10000000 records, only one record that matches cat_id = 1
So it will not waste a lot of time and space to copy tp_goods to VT1?

So it should be:
Create a virtual table VT (only the cat_id, goods_name, and goods_price columns are available)
Traverse tp_goods and append the records that match cat_id = 1 to VT.
Output VT content

For select cat_id, max (goods_price) as max_price from tp_goods group by cat_id
The execution process should be:
Create a virtual table VT (only cat_id and max_price columns are supported, and cat_id is identified as clustering, max_price is calculated)
Traverse tp_goods and append the record to VT. if the value of tp_goods.cat_id already exists in VT. cat_id. Modify the corresponding max_price to max (goods_price, max_price); otherwise, append
Output VT content

The execution sequence you understand is incorrect, at least not scientific.
If tp_goods has 10000000 records, only one record that matches cat_id = 1
So it will not waste a lot of time and space to copy tp_goods to VT1?

So it should be:
Create a virtual table VT (only the cat_id, goods_name, and goods_price columns are available)
Traverse tp_goods and append the records that match cat_id = 1 to VT.
Output VT content

For select cat_id, max (goods_price) as max_price from tp_goods group by cat_id
The execution process should be:
Create a virtual table VT (only cat_id and max_price columns are supported, and cat_id is identified as clustering, max_price is calculated)
Traverse tp_goods and append the record to VT. if the value of tp_goods.cat_id already exists in VT. cat_id. Modify the corresponding max_price to max (goods_price, max_price); otherwise, append
Output VT content

Moderator: is there any book recommendation that involves explanation of the execution process?

There are certainly some books, probably in the 1990 s. But I did not see it (I may not see it for me)

According to the process, the process can be simply simulated by using the php associated array.
Mysql developers won't be mentally retarded to use the process you heard to operate it.

There are certainly some books, probably in the 1990 s. But I did not see it (I may not see it for me)

According to the process, the process can be simply simulated by using the php associated array.
Mysql developers won't be mentally retarded to use the process you heard to operate it.

How to simulate it?
Therefore, I want to know how mysql developers operate in programs.

According to the moderator, is it true that only one virtual table is generated? If multiple select statements exist, will there be multiple virtual tables? For example:
Select * from tp_goods where cat_id = (select id from tp_cat where cat_name = 'phone ');
Does the moderator explain the execution process of the first step of a complicated statement, including where, group by, having, orderby?

1. the from clause is used to assemble data from different data sources. 2. the where clause is used to filter data based on specified conditions. 3. the group by clause is used to divide data into multiple groups. 4. use the aggregate function for calculation. 5. use the having clause to filter groups. 6. calculate all expressions 7. sort all result sets using order
When we encounter the above brackets, we certainly execute them according to the internal brackets, such as the above priority.

Run the question: Is the Apple 6 in your table true?

I checked "High-Performance MySQL" (version 2) and did not find the description of the query sequence described by the landlord. I hope the following will help you.