What's the most efficient way to select the last n rows in a table without changing the table's structure?

Question

What's the most efficient way to select the last n number of rows in a table using mySQL? The table contains millions of rows, and at any given time I don't know how large the table is (it is constantly growing). The table does have a column that is automatically incremented and used as a unique identifier for each row.

Paddle

Answer 1

SELECT * FROM table_name ORDER BY auto_incremented_id DESC LIMIT n

Hazem

Answer 2

Actually the right way to get last n rows in order is to use a subquery:

(SELECT id, title, description FROM my_table ORDER BY id DESC LIMIT 5) 
ORDER BY tbl.id ASC

As this way is the only I know that will return them in right order. The accepted answer is actually a solution for "Select first 5 rows from a set ordered by descending ID", but that is most probably what you need.

Sixth

Maybe late, but I've just found from here that you can simply omit the external select and alias, and just have: (SELECT * FROM table ORDER BY id DESC LIMIT 50) ORDER BY id ASC; although it may be a little confusing to read. – Husha

@Husha that is really neat, it's much better, than mine solution and it's not so confusing, I will add that to my answer :) – Sixth

Answer 3

(Similar to "marco"s answer,)
my fav is the max()-function of MySQL too, in a simple one-liner, but there are other ways of sure:

SELECT whatever FROM mytable WHERE id > (SELECT max(id)-10 FROM mytable);

... and you get "last id minus 10", normally the last 10 entries of that table.

It's a short way, to avoid the a error 1111 ("Invalid use of group function") not only if there is a auto_increment-row (here id).
The max()-function can be used many ways.

Antipas

I benchmarked all the responses with my table of over one million results and this way was the fastest. Actually 10 milliseconds faster than sorting by ID. – Derringer

Answer 4

Maybe order it by the unique id descending:

SELECT * FROM table ORDER BY id DESC LIMIT n

The only problem with this is that you might want to select in a different order, and this problem has made me have to select the last rows by counting the number of rows and then selecting them using LIMIT, but obviously that's probably not a good solution in your case.

Everyway

Answer 5

Use ORDER BY to sort by the identifier column in DESC order, and use LIMIT to specify how many results you want.

Faddish

Answer 6

You would probably also want to add a descending index (or whatever they're called in mysql) as well to make the select fast if it's something you're going to do often.

Psychosomatic

Answer 7

This is a lot faster when you have big tables because you don't have to order an entire table. You just use id as a unique row identifier. This is also more eficient when you have big amounts of data in some colum(s) as images for example (blobs). The order by in this case can be very time and data consuming.

select * 
from TableName 
where id > ((select max(id) from TableName)-(NumberOfRowsYouWant+1)) 
order by id desc|asc

The only problem is if you delete rows in the interval you want. In this case you would't get the real "NumberOfRowsYouWant".

You can also easily use this to select n rows for each page just by multiplying (NumberOfRowsYouWant+1) by page number when you need to show the table backwards in multiple web pages.

Insinuate

Answer 8

Here you can change table name and column name according your requirement . if you want to show last 10 row then put n=10,or n=20 ,or n=30 ...etc according your requirement.

select * from (select * from employee Order by emp_id desc limit n) a Order by emp_id asc;

Anuska