[mysql] mysql select from n last rows

I have a table with index (autoincrement) and integer value. The table is millions of rows long.

How can I search if a certain number appear in the last n rows of the table most efficiently?

Last 5 rows retrieve in mysql

This query working perfectly

SELECT * FROM (SELECT * FROM recharge ORDER BY sno DESC LIMIT 5)sub ORDER BY sno ASC

or

select sno from(select sno from recharge order by sno desc limit 5) as t where t.sno order by t.sno asc

because it is autoincrement, here's my take:

Select * from tbl 
where certainconditionshere 
and autoincfield >= (select max(autoincfield) from tbl) - $n

Might be a very late answer, but this is good and simple.

select * from table_name order by id desc limit 5

This query will return a set of last 5 values(last 5 rows) you 've inserted in your table

I know this may be a bit old, but try using PDO::lastInsertId. I think it does what you want it to, but you would have to rewrite your application to use PDO (Which is a lot safer against attacks)

Take advantage of SORT and LIMIT as you would with pagination. If you want the ith block of rows, use OFFSET.

SELECT val FROM big_table
where val = someval
ORDER BY id DESC
LIMIT n;

In response to Nir: The sort operation is not necessarily penalized, this depends on what the query planner does. Since this use case is crucial for pagination performance, there are some optimizations (see link above). This is true in postgres as well "ORDER BY ... LIMIT can be done without sorting " E.7.1. Last bullet

explain extended select id from items where val = 48 order by id desc limit 10;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | items | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index | 
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+