CODEWITHSUNDEEP
mysqlselectquery-optimization
Nir
Nir

Reputation: 25349

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?

Upvotes: 69

Views: 153618

Answers (6)

Suganthan Madhavan Pillai
Suganthan Madhavan Pillai

Reputation: 5655

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

Upvotes: 20

Luke Madhanga
Luke Madhanga

Reputation: 7447

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)

Upvotes: 0

M Palani Mca
M Palani Mca

Reputation: 159

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

Upvotes: 14

Dana the Sane
Dana the Sane

Reputation: 15198

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

Upvotes: 13

Michael Buen
Michael Buen

Reputation: 39393

because it is autoincrement, here's my take:

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

Upvotes: 3

Bill Karwin
Bill Karwin

Reputation: 562230

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;

Upvotes: 95

Related Questions