MYSQL - ORDER BY & LIMIT

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.

Answer 1

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.

Answer 2

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

Answer 3

You can use this code SELECT article FROM table1 ORDER BY publish_date LIMIT 0,10 where 0 is a start limit of record & 10 number of record

Answer 4

SELECT * FROM table1 ORDER BY id asc LIMIT 5 OFFSET 0

The query must be like this

Answer 5

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.

Answer 6

Also syntax of LIMIT is different according to databases, for example:

mysql - LIMIT 1, 2

postgres - LIMIT 2 OFFSET 1