Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ORDER BY NULL in MySQL

What is ORDER BY NULL in MySQL?

Does it decrease the query speed?

like image 980
Mahoor13 Avatar asked Mar 08 '11 11:03

Mahoor13


People also ask

How do you ORDER BY NULL?

If you specify the ORDER BY clause, NULL values by default are ordered as less than values that are not NULL. Using the ASC order, a NULL value comes before any non-NULL value; using DESC order, the NULL comes last.

What does ORDER BY NULL mean?

using ORDER BY NULL is a workaround that satifies the syntax requirement but does not actually change the order of the data. In effect it is an instruction to not order at all. N.B.: some (myself included) prefer to use SELECT 1 instead of SELECT NULL but there is no difference in effect.

How is NULL treated in ORDER BY?

If you sort a column with NULL values in ascending order, the NULLs will come first. Alternatively, if you add a DESC keyword to get a descending order, NULLs will appear last.

Is NULL () in MySQL?

Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values. Because the result of any arithmetic comparison with NULL is also NULL , you cannot obtain any meaningful results from such comparisons. In MySQL, 0 or NULL means false and anything else means true.


1 Answers

It's for performance; adding ORDER BY NULL after a GROUP BY clause will make your query faster.

An explanation, from the manual:

By default, MySQL sorts all GROUP BY col1, col2, ... queries as if you specified ORDER BY col1, col2, ... in the query as well. If you include an explicit ORDER BY clause that contains the same column list, MySQL optimizes it away without any speed penalty, although the sorting still occurs. If a query includes GROUP BY but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying ORDER BY NULL. For example:

INSERT INTO foo SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL; 

This article describes the author successfully optimising a slow query by exploiting this trick, complete with the relevant parts of the EXPLAIN output.

like image 126
Haim Evgi Avatar answered Oct 05 '22 22:10

Haim Evgi