How to Efficiently Search for a Number in the Last N Rows of a Large MySQL Table?

Searching for a Number in Last N Rows of a Large MySQL Table

In this scenario, you have a lengthy table with millions of rows, each having an index (autoincrement) and an integer field. You want to quickly determine whether a specified number appears in the final n rows of the table.

Efficient Solution

The most effective approach entails leveraging a derived table as follows:

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

Explanation:

• This query establishes a derived table, selecting the id and val columns from the big_table.
• It sorts the derived table in descending order based on the id column, ensuring that the most recent rows appear first.
• The LIMIT $n clause restricts the result set to the most recent n rows.
• Finally, the WHERE condition matches the derived table's val column against the supplied certain_number, identifying rows containing the number in the last n rows.

Benefits:

• Indexed Search: Sorting by the autoincrement id column takes advantage of the table index, optimizing the search.
• Performance: Limiting the result set to the last n rows eliminates the need to scan the entire table, significantly enhancing performance.
• Flexibility: The query allows you to modify the $n parameter to search within different ranges of the last rows.

The above is the detailed content of How to Efficiently Search for a Number in the Last N Rows of a Large MySQL Table?. For more information, please follow other related articles on the PHP Chinese website!