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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

LIMIT is usually applied as the last operation  so the result will first be sorted and then limited to 20  In fact  sorting will stop as soon as first 20 sorted results are found

User · Answer

You can use this code  SELECT article FROM table1 ORDER BY publish date LIMIT 0 10 where 0 is a start limit of record  amp  10 number of record

User · Answer

Could be simplified to this  SELECT article FROM table1 ORDER BY publish date DESC FETCH FIRST 20 ROWS ONLY  You could also add many argument in the ORDER BY that is just comma separated like  ORDER BY publish date  tab2  tab3 DESC etc

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

Examples related to mysql

Examples related to sql

Examples related to sql-order-by

Examples related to sql-limit