Efficient Sorting with Multiple Conditions in MySQL（mysql多条件排序）

The development of big data and the application of relational database system have brought tremendous changes to the Internet era. Among various relational databases, MySQL is a very popular RDBMS. Of course, the most important thing in the operation of the database, in addition to the basic operations such as the addition, deletion and modification of data, is the sorting of data.

In MySQL, sorting of data can be done using the SELECT statement. For example, if you want to sort the student table according to the students’ scores from high to low, you can use the following statement:

SELECT *

FROM student

ORDER BY score DESC;

However, sometimes we may need to sort data according to multiple conditions. For example, if the students’ scores are the same, then sort according to the students’ ages from young to old. Then, the syntax of the statement should be changed as follows:

SELECT *

FROM student

ORDER BY score DESC, age ASC;

It can be seen that the syntax has not changed much, only the addition of a condition and the change of the sorting order. This can be further simplified. If you are using a storage engine that supports the composite index, you can use the following statement:

CREATE INDEX index_name

ON table_name (column1, column2);

SELECT * FROM table_name ORDER BY column1 DESC, column2 ASC;

This statement can make the sorting more efficient, because the index has been created and the records do not need to be read one by one when sorting. Therefore, efficient sorting can be achieved with multiple conditions when using MySQL.