Is MySQL LIMIT applied before or after ORDER BY?
Solution 1
Yes, it's after the ORDER BY. For your query, you'd get the record with the highest publishedOn, since you're ordering DESC
, making the largest value first in the result set, of which you pick out the first one.
Solution 2
The limit
is always applied at the end of result gathering, therefore after order by
.
Given all your clauses, the order of processing will be
- FROM
- WHERE
- SELECT
- ORDER BY
- LIMIT
So you will get the closest record <= publishedOn matching all the conditions in the WHERE clause.
Solution 3
Just wanted to point out the in case of MySQL ordering is applied before limiting the results. But this is not true for other DB.
For example Oracle first limits results and applies ordering on said results. It makes sense when you think about it from a performance point of view. In MySQL you are actually ordering the entire DB(> 1000 records) to get 2
Chris Abrams
Updated on November 18, 2020Comments
-
Chris Abrams over 3 years
Which one comes first when MySQL processes the query?
An example:
SELECT pageRegions FROM pageRegions WHERE(pageID=?) AND(published=true) AND (publishedOn<=?) ORDER BY publishedON DESC LIMIT 1';
Will that return the last published pageRegion even if the record does not match the revision datetime IF LIMIT is applied after ORDER BY?
-
Alastair over 10 yearsGood to know, thanks, @cristian ! I was curious about this from a performance perspective.
-
1111161171159459134 almost 9 yearsThis reply is long time ago. I just want to point out out that MySQL belongs to Oracle like other Oracle DBs (as of 2015), and according to this MySQL page no full table scan is done as soon as you use
LIMIT
whether you addORDER BY
or not. -
ToolmakerSteve over 5 years@1111161171159459134 - but note this quote from the page you linked: " If you combine LIMIT row_count with ORDER BY, MySQL stops sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause are selected, and most or all of them are sorted, before the first row_count are found." So if you don't have an index for the specified ordering, its essentially a full table scan.