Usage and optimization of MySQL order by sort statement

MySQL ORDER BY syntax

The code is as follows	Copy Code
SELECT column_name (s) from table_name ORDER BY column_name

NOTE: The SQL statement is a "letter case insensitive" statement (it does not distinguish between uppercase and lowercase letters), that is, "order by" is the same as the "by".

MySQL ORDER BY case

The following example: Select all the records from the person table and categorize the Age column:

The code is as follows	Copy Code
<?php $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"); while ($row = Mysql_fetch_array ($result)) { echo $row [' FirstName '] echo "". $row [' LastName ']; echo "". $row [' age ']; echo "<br/>"; } Mysql_close ($con); ?>

The above code will output the following result:

Glenn Quagmire 33

Peter Griffin 35

Sort by ascending or descending order

If you use the "ORDER BY" keyword, all records will be sorted by default ascending (i.e.: from 1 to 9, from A to Z)

Use the "DESC" keyword to make all the data in descending order (that is, from 9 to 1, from Z to a):

Double-click the code selection 123 Select COLUMN_NAME (s) from table_name ORDER BY column_name DESC

MySQL order BY is sorted by two columns

Many times, we need to classify data based on two columns of content (or more). When the specified number of columns is more than one column, the second column is referenced only when the value of the first column is exactly the same:

Double-click the code selection 123 Select COLUMN_NAME (s) from table_name ORDER by COLUMN_NAME1, column_name2

By optimizing the index to achieve MySQL's ORDER BY statement optimization:

1. Index optimization by. If an SQL statement is shaped like this:

The code is as follows	Copy Code
SELECT [Column1],[column2],.... From [TABLE] order by [sort];

Indexing on the [Sort] field enables the use of an index for order by optimization.
2, where + order by index optimization, such as:

SELECT [Column1],[column2],.... From [TABLE] WHERE [COLUMNX] = [value] order by [sort];
Establish a federated index (Columnx,sort) to implement order by optimization.

Note: If COLUMNX corresponds to multiple values, the following statement cannot use the index to implement an order by optimization

The code is as follows	Copy Code
SELECT [Column1],[column2],.... From [TABLE] WHERE [ColumnX] In ([Value1],[value2],...) Order By[sort];

3, where+ multiple fields order by

The code is as follows	Copy Code
SELECT * from [table] WHERE uid=1 order x,y LIMIT 0, 10;

Establishing an index (UID,X,Y) to implement an order by optimization is much better than establishing a (X,Y,UID) index.

MySQL order by cannot use index to optimize sorting

* The different index keys do ORDER BY: (Key1,key2 index)

The code is as follows	Copy Code
SELECT * from T1-Key1, Key2;

* Make an ORDER by: (Key_part1,key_part2 to establish a federated index; Key2 index) on a noncontiguous index key section

The code is as follows	Copy Code
SELECT * from T1 WHERE key2=constant order by Key_part2;

* Simultaneous use of ASC and DESC: (Key_part1,key_part2 to establish a federated index)

The code is as follows	Copy Code
SELECT * from T1 ORDER by Key_part1 DESC, Key_part2 ASC;

* The index keys used to search for records are not the same as those made by: (Key1,key2 indexed separately)

The code is as follows	Copy Code
SELECT * from T1 WHERE key2=constant order by Key1;

* If an expression (function) is applied on the field of the Where and order by, the index cannot be used to implement an order by optimization

The code is as follows	Copy Code
SELECT * from T1 (logindate) LIMIT 0, 10;

Special tips:

>mysql a query can only use one index at a time. If you want to use indexes on multiple fields, create a composite index.

> Smaller data types are generally better: smaller data types typically require less space in disk, memory, and CPU caching, and are faster to handle.
> Simple data types are better: integer data is less expensive to process than characters, because strings are more complex. In MySQL, you should use a built-in date and time data type instead of a string to store the time, and an integer data type to store the IP address.
> try to avoid null: you should specify NOT NULL unless you want to store null. In MySQL, columns with null values are difficult to query optimization because they make indexing, indexing, and comparison operations more complex. You should use 0, a special value, or an empty string instead of a null value.
> In an order by operation, MySQL uses indexes only if the sort criteria are not a query condition expression.