MySQLORDERBY sorting statement usage and Optimization

In mysql, ORDERBYkeyword is used to classify the data in the record. Next I will summarize the methods for optimizing orderby statements using indexes.

In mysql, order by keyword is used to classify the data in the record. Next I will summarize the method used by the order BY statement to optimize the index.

MySQL Order By syntax

The Code is as follows:
SELECT column_name (s) FROM table_name order by column_name

Note: The SQL statement is a "case-insensitive" Statement (which does not distinguish between uppercase and lowercase letters), that is, "ORDER BY" and "order by" are the same.

MySQL Order By case

The following example shows how to select all records from the "Person" table and classify the "Age" column:

The Code is as follows:
$ Con = mysql_connect ("localhost", "peter", "abc123 "); If (! $ Con) { Die ('could not connect: '. mysql_error ()); } Mysql_select_db ("my_db", $ con ); $ Result = mysql_query ("SELECT * FROM person order by age "); While ($ row = mysql_fetch_array ($ result )) { Echo $ row ['firstname'] Echo "". $ row ['lastname']; Echo "". $ row ['age']; Echo" "; } Mysql_close ($ con ); ?>

The above code will output the following results:

Glenn Quagmire 33

Peter Griffin 35

Sort by ascending or descending order

If you use the "order by" keyword, all records are sorted in ascending order by default (from 1 to 9, from a to z)

Use the "DESC" keyword to sort all data in descending order (from 9 to 1, from z to ):

Double-click the code to SELECT 123 SELECT column_name (s) FROM table_name order by column_name DESC.

MySQL Order By is classified By two columns

Most of the time, we need to classify data based on the two columns (or more columns) at the same time. When the specified number of columns is greater than one, the second column is referenced only when the values of the first column are identical:

Double-click the code to SELECT 123 SELECT column_name (s) FROM table_name order by column_name1, column_name2

Optimize the order by statement of MySQL through index optimization:

1. order by index optimization. If an SQL statement is like:

The Code is as follows:
SELECT [column1], [column2],... FROM [TABLE] order by [sort];

You can create an index on the [sort] column to optimize order by using the index.
2. Optimize the index of WHERE + order by, for example:

SELECT [column1], [column2],... FROM [TABLE] WHERE [columnX] = [value] order by [sort];
Create a joint index (columnX, sort) to optimize order.

NOTE: If columnX corresponds to multiple values, the following statement cannot optimize order by using indexes.

The Code is as follows:
SELECT [column1], [column2],... FROM [TABLE] WHERE [columnX] IN ([value1], [value2],…) Order by [sort];

3. WHERE + multiple fields ORDER

The Code is as follows:
SELECT * FROM [table] WHERE uid = 1 ORDER x, y LIMIT 0, 10;

Creating an index (uid, x, y) to optimize order by is much better than creating an index (x, y, uid.

MySQL Order By cannot use indexes to optimize sorting

* Perform order by on different index keys: (create indexes for key1 and key2 respectively)

The Code is as follows:
SELECT * FROM t1 order by key1, key2;

* Order by: (key_part1 and key_part2 are used to create a joint index; key2 is used to create an index)

The Code is as follows:
SELECT * FROM t1 WHERE key2 = constant order by key_part2;

* Both ASC and DESC :( key_part1 and key_part2 are used to create a joint index)

The Code is as follows:
SELECT * FROM t1 order by key_part1 DESC, key_part2 ASC;

* The index key used for searching records and the order by statement are not the same: (key1 and key2 create indexes respectively)

The Code is as follows:
SELECT * FROM t1 WHERE key2 = constant order by key1;

* If expressions (functions) are applied in the WHERE and order by columns, indexes cannot be used to optimize order.

The Code is as follows:
SELECT * FROM t1 order by year (logindate) LIMIT 0, 10;

Note:

> Only one index can be used for one mysql query at a time. If you want to use an index for multiple fields, create a composite index.

> Smaller data types are generally better: smaller data types usually require less space in the disk, memory, and CPU cache for faster processing.
> A simple data type is better: the processing overhead of integer data is lower than that of characters because the strings are more complex. In MySQL, the built-in Date and Time data types should be used instead of strings to store the time, and the IP addresses of integer data types should be used to store the time.
> Avoid NULL as much as possible: the column should be specified as not null unless you want to store NULL. In MySQL, it is difficult to query and optimize columns with null values, because they make the index and index statistics and comparison operations more complex. You should replace null values with 0, a special value, or an empty string.
> In the order by operation, MySQL uses indexes only when the sorting condition is not a query condition expression.