Menu
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?
299
Try only this:- SELECT * FROM reset ORDER BY ASC LIMIT (FOUND_ROWS() - 3), 3 and check if it is giving the last 3 rows from your table in ascending order!!!
Let us now implement the above query. mysql> SELECT * FROM ( -> SELECT * FROM Last10RecordsDemo ORDER BY id DESC LIMIT 10 -> )Var1 -> -> ORDER BY id ASC; The following is the output that displays the last 10 records. We can match both records with the help of the SELECT statement.
METHOD 1 : Using LIMIT clause in descending order As we know that LIMIT clause gives the no. of specified rows from specifies row. We will retrieve last 5 rows in descending order using LIMIT and ORDER BY clauses and finally make the resultant rows ascending.
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; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
