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?

Solution 1:

Starting from the answer given by @chaos, but with a few modifications:

You should always use ORDER BY if you use LIMIT. There is no implicit order guaranteed for an RDBMS table. You may usually get rows in the order of the primary key, but you can't rely on this, nor is it portable.
If you order by in the descending order, you don't need to know the number of rows in the table beforehand.
You must give a correlation name (aka table alias) to a derived table.

Here's my version of the query:

SELECT `id`
FROM (
    SELECT `id`, `val`
    FROM `big_table`
    ORDER BY `id` DESC
    LIMIT $n
) AS t
WHERE t.`val` = $certain_number;

Solution 2:

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

Solution 3:

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

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

Solution 4:

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 | 
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

Solution 5:

because it is autoincrement, here's my take:

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