Explanation of the execution priority of mySQL keywords

As shown below:

from table

where condition

group by field

Form virtual tables and fields, aggregate and add fields

having filter data

distinct

order by field asc,desc

limit limit number of entries

Select List Records

First, the data is loaded from the table into memory according to the where constraint, so the where condition acts before the data, and then grouped according to the field, forming a virtual table in memory containing the field.

If there is an aggregate function at this time, the aggregate function is executed first, and the aggregate function field is also added to the virtual table. Then the having record filter is performed. After the filtering is completed, the data is deduplicated, sorted, restricted, and other operations are performed before display.

Additional knowledge: The execution order of mysql where conditions and is from left to right

When I was working on this function before, I encrypted the three conditions through md5 to get a string field unique_key, and set this field as the unique index. I hope to use this unique index when searching, without having to search for the three conditions separately. At the same time, I used the replace method to ensure that if the data is updated under the same three conditions, there will be only one record in the database.

Later I found that the list query must open these three conditions, so I had to put the unique_key to the left of the where condition and, and the efficiency of filtering by unique_key would not be bad.

You should consider all aspects of MySQL optimization when writing.

The above explanation of the execution priority of mySQL keywords is all I want to share with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.