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

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

Answer 3

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.

Answer 4

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.