To fetch last 10 record ,adding 'LIMIT' to MySQL queries make it faster?

Question

Can someone explain if i want to fetch last 10 records ,adding 'LIMIT' to MySQL queries make it faster? like

SELECT 
    *
FROM
    `table`
ORDER BY id DESC
LIMIT 50;

is there any other way to fetch last 10 record or is it faster?

Answer 1

The problem with limit is it must order all the rows in the table before returning only the last 50, so it's always going to be slow and will get slower as the table grows (not scaleable).

The fastest way to get the last 50 is:

SELECT 
  *
FROM
  `table`
WHERE id > (SELECT MAX(id) FROM `table`) - 50

This will use the index on id to get the max id and again to get the top 50.

---

You should also try this slightly more complicated but potentially better optimisable:

SELECT 
  *
FROM
  `table`
WHERE id BETWEEN (SELECT MAX(id) FROM `table`) - 49
    AND (SELECT MAX(id) FROM `table`)

Compare actual run times andcuse the fastest.