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?
Add a comment |
6 Answers
Starting from the answer given by @chaos, but with a few modifications:
You should always use
ORDER BYif you useLIMIT. 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; 
7 Comments
Ray Hidayat
I would upvote this several times if I could, all the other answers are buggy. This is what I would've posted too.
Bill Karwin
Note that the answer from @chaos to which I refer has been deleted.
Nir
Thanks! How many records will the engine have to scan to get the answer? Is it n? or m= the location of val from the top?
Bill Karwin
I assume it would have to scan only n rows. The ORDER BY...LIMIT can be done using an index, without scanning rows.
Bill Karwin
@JSHelp, the OP asks how to check if a certain number appears in the last n rows. So
$certain_number is the value that the OP is searching for. $n is not the size of the result, it's the number of rows to search. The size of the result may be just one, if val is a unique column, or it could be up to $n, or it could be zero rows if the value is not found. |
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
answered Sep 14, 2014 at 13:02
Suganthan Madhavan Pillai
5,64510 gold badges59 silver badges86 bronze badges
1 Comment
jww
What value does this provide? The question was answered six years ago. The code snippet appears to be a subset of previously provided answers, and it has not explanation.
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 
Comments
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 | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ 
4 Comments
Bill Karwin
You can't use MAX() or COUNT() in a WHERE clause.
Dana the Sane
Case doesn't strip out NULL's so I removed that example altogether.
Nir
In the current example the db engine will have to look through all n rows even if it finds the value at the first one. Not too omptimized.
Dana the Sane
In my non-stringent test, mysql 5.0.x uses an index, as above.
because it is autoincrement, here's my take:
Select * from tbl where certainconditionshere and autoincfield >= (select max(autoincfield) from tbl) - $n 
2 Comments
Itay Moav -Malimovka
I would just change the order of conditions in the WHERE clause.
Bill Karwin
This answer assumes the last $n rows have contiguous id values.
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)
