MYSQL ORDER BY
Contexts
The clauses in a SELECT have a specific order:
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ...
ORDER BY ... -- goes here
LIMIT ... OFFSET ...;
( SELECT ... ) UNION ( SELECT ... ) ORDER BY ... -- for ordering the result of the UNION.
SELECT ... GROUP_CONCAT(DISTINCT x ORDER BY ... SEPARATOR ...) ...
ALTER TABLE ... ORDER BY ... -- probably useful only for MyISAM; not for InnoDBBasic
ORDER BY x
x can be any datatype.
.NULLs precede non-NULLs.
.The default is ASC (lowest to highest)
.Strings (VARCHAR, etc) are ordered according the COLLATION of the declaration
.ENUMs are ordered by the declaration order of its strings.
ASCending / DESCending
ORDER BY x ASC -- same as default
ORDER BY x DESC -- highest to lowest
ORDER BY lastname, firstname -- typical name sorting; using two columns
ORDER BY submit_date DESC -- latest first
ORDER BY submit_date DESC, id ASC -- latest first, but fully specifying order..ASC = ASCENDING, DESC = DESCENDING
.NULLs come first even for DESC.
.In the above examples, INDEX(x), INDEX(lastname, firstname), INDEX(submit_date) may significantly improve performance.
But... Mixing ASC and DESC, as in the last example, cannot use a composite index to benefit. Nor will INDEX(submit_date DESC, id ASC) help -- "DESC" is recognized syntactically in the INDEX declaration, but ignored.