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.

Answer 1

SELECT * FROM table_name ORDER BY auto_incremented_id DESC LIMIT n

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.

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.