mysql select from n last rows

Question

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?

Answer 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;