CODEWITHSUNDEEP
mysqlsql
thegad
thegad

Reputation: 193

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

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.

Upvotes: 19

Views: 28837

Answers (8)

Vipin Pandey
Vipin Pandey

Reputation: 907

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;

Upvotes: 0

tron5
tron5

Reputation: 500

(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.

Upvotes: 5

Sergey Telshevsky
Sergey Telshevsky

Reputation: 12197

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.

Upvotes: 16

Marco
Marco

Reputation: 11

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.

Upvotes: 1

Eran Galperin
Eran Galperin

Reputation: 86805

SELECT * FROM table_name ORDER BY auto_incremented_id DESC LIMIT n

Upvotes: 46

Ray
Ray

Reputation: 46565

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.

Upvotes: 2

Paige Ruten
Paige Ruten

Reputation: 176635

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.

Upvotes: 4

Avdi
Avdi

Reputation: 18418

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

Upvotes: 2

Related Questions