How does MySQL process ORDER BY and LIMIT in a query?

Question

I have a query that looks like this:

SELECT article FROM table1 ORDER BY publish_date LIMIT 20

How does ORDER BY work? Will it order all records, then get the first 20, or will it get 20 records and order them by the publish_date field?

If it's the last one, you're not guaranteed to really get the most recent 20 articles.

Solution 1

It will order first, then get the first 20. A database will also process anything in the WHERE clause before ORDER BY.

Solution 2

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

With one argument, the value specifies the number of rows to return from the beginning of the result set:

SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows

In other words, LIMIT row_count is equivalent to LIMIT 0, row_count.

All details on: http://dev.mysql.com/doc/refman/5.0/en/select.html

Solution 3

Just as @James says, it will order all records, then get the first 20 rows.

As it is so, you are guaranteed to get the 20 first published articles, the newer ones will not be shown.

In your situation, I recommend that you add desc to order by publish_date, if you want the newest articles, then the newest article will be first.

If you need to keep the result in ascending order, and still only want the 10 newest articles you can ask mysql to sort your result two times.

This query below will sort the result descending and limit the result to 10 (that is the query inside the parenthesis). It will still be sorted in descending order, and we are not satisfied with that, so we ask mysql to sort it one more time. Now we have the newest result on the last row.

select t.article 
from 
    (select article, publish_date 
     from table1
     order by publish_date desc limit 10) t 

order by t.publish_date asc;

If you need all columns, it is done this way:

select t.* 
from 
    (select * 
     from table1  
     order by publish_date desc limit 10) t 

order by t.publish_date asc;

I use this technique when I manually write queries to examine the database for various things. I have not used it in a production environment, but now when I bench marked it, the extra sorting does not impact the performance.

Solution 4

You could add [asc] or [desc] at the end of the order by to get the earliest or latest records

For example, this will give you the latest records first

ORDER BY stamp DESC

Append the LIMIT clause after ORDER BY

Solution 5

If there is a suitable index, in this case on the publish_date field, then MySQL need not scan the whole index to get the 20 records requested - the 20 records will be found at the start of the index. But if there is no suitable index, then a full scan of the table will be needed.

There is a MySQL Performance Blog article from 2009 on this.

View more solutions

Comments

[Konrad Morawski]

Note that if some publish_dates are equal, ordering by them does not give determinate results, meaning that if you use LIMIT for pagination, you may end up getting the same items on different pages!

[Kalko]

Watch out for the order in which you apply these. If you do LIMIT first and then ORDER BY, it will throw an error. ORDER BY must be first in the query.

[Leigh]

Welcome to stackoverflow. I think you may have misunderstood the question. I believe they were asking about the order of operations rather than "how to sort". (But it is moot since the question was already answered a while ago ;)

[Sukhpreet Singh Alang]

Your second sentence goes against your first. Sorting cannot stop when the first 20 results are found because as you said sorting will be done before the results will be returned. MySQL can only know what the first 20 results are after sorting has finished.

[Adonis K. Kakoulidis]

Isn't it retrieve rows 5-14?

[Lawrence Dol]

No that's not required. LIMIT 10 is shorthand for LIMIT 0,10.

[dcaswell]

@adonis No, it's not. The example is right from the MySQL Documentation

[Phil Perry]

Number 5 is the 6th row. 5 rows (0 through 4) are ignored.

[Barton]

But using LIMIT without ORDER BY may give inconsistent results! Unfortunately, the entire result set must be ordered before the LIMIT is applied or the the DBMS is free to arbitrarily order the result and then OFFSET and LIMIT on that set. I've read that this may be due to the DBMS selecting an alternate Query Plan based on OFFSET and LIMIT thus the arbitrary order.

[gaurangkathiriya]

yes, not required for LIMIT 0,10 But You Can required Like This Limit 10,20

[Martin Kersten]

Your extra sorting can virtually not have any measurable impact on performance since it is limited to 10 rows / items only :-). In general sorting an in-memory table (which a sub-select is producing) is very fast and barely measurable unless you have millions of rows or the DBMS is paging the result set to disk since it does not fit the memory (in which case depending on the DBMS it can also abort the query).

[Yasar Arafath]

So timing is same ?

[Green]

Wrong! LIMIT breaks ORDER BY. With LIMIT an ORDER BY returns wrong result. LIMIT somehow reorders the result set returned by ORDER BY

[yitwail]

@Green, you're mistaken. Read this for the explanation: dev.mysql.com/doc/refman/5.7/en/limit-optimization.html When the ORDER BY column is indexed, it may return records in a different order than without the LIMIT, when there are more than 1 records with the same value in that column.

[yitwail]

@Tom, actually it can, if ordering by an indexed column. It's explained here:dev.mysql.com/doc/refman/5.7/en/limit-optimization.html

[rineez]

One quick solution for such problems is to add one more column to order by preferably having unique values so that database gets a consistent rule for ordering rows when value of the first order-by-column is same for multiple rows.

[Shen liang]

question is asking the limit & order by. But the answer is not related to this question at all