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?

score 95 · Accepted Answer · edited May 23 '17 at 12:25

95

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;

edited May 23 '17 at 12:25

Community

1
1

answered Feb 22 '09 at 01:52

Bill Karwin

538,548
86
673
828

1

I would upvote this several times if I could, all the other answers are buggy. This is what I would've posted too. – Ray Hidayat Feb 22 '09 at 03:07
3

Note that the answer from @chaos to which I refer has been deleted. – Bill Karwin Feb 22 '09 at 04:26
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? – Nir Feb 22 '09 at 12:51
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 Feb 22 '09 at 16:51
@BillKarwin what should `$certain_number` be? `$n` is obviously the size of the result. Then I would up vote this – Deproblemify Jul 21 '14 at 11:30
1

@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. – Bill Karwin Jul 21 '14 at 14:20
@BillKarwin got it. I have found, what I have been searching for [in this post](http://stackoverflow.com/questions/12125904/select-last-n-rows-from-mysql) – Deproblemify Jul 21 '14 at 14:36

Suganthan Madhavan Pillai · Answer 2 · 2014-09-21T11:44:55.013

20

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

edited Sep 21 '14 at 11:44

answered Sep 14 '14 at 13:02

Suganthan Madhavan Pillai

5,495
9
51
84

3

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. – jww Sep 14 '14 at 13:25

score 14 · Answer 3 · edited Sep 02 '13 at 07:49

14

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

edited Sep 02 '13 at 07:49

Ankur

5,086
19
37
62

answered Sep 02 '13 at 07:27

M Palani Mca

159
1
3

Dana the Sane · Answer 4 · 2009-02-22T19:05:09.663

13

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

edited Feb 22 '09 at 19:05

answered Feb 21 '09 at 23:16

Dana the Sane

14,762
8
58
80

You can't use MAX() or COUNT() in a WHERE clause. – Bill Karwin Feb 22 '09 at 01:53
Case doesn't strip out NULL's so I removed that example altogether. – Dana the Sane Feb 22 '09 at 07:30
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. – Nir Feb 22 '09 at 08:37
In my non-stringent test, mysql 5.0.x uses an index, as above. – Dana the Sane Feb 22 '09 at 19:10

score 3 · Answer 5 · answered Feb 22 '09 at 02:36

3

because it is autoincrement, here's my take:

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

answered Feb 22 '09 at 02:36

Michael Buen

38,643
9
94
118

I would just change the order of conditions in the WHERE clause. – Itay Moav -Malimovka Feb 22 '09 at 02:44
4

This answer assumes the last $n rows have contiguous id values. – Bill Karwin Feb 22 '09 at 03:05

score 0 · Answer 6 · answered Oct 22 '13 at 20:14

0

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)

answered Oct 22 '13 at 20:14

Luke Madhanga

6,871
2
43
47

mysql select from n last rows

6 Answers6

Linked

Related