Date: 2009-09-30 11:53 pm (UTC)
From: [identity profile] zanfur.livejournal.com
If you're fine using subselects, this works like a champ, with N being the id of the unique item in question:

select * from items where id >= (select max(id) from items where id < N) limit 3

The subselect is (as above) is optimized away to a constant-time index lookup, and the item retrieval is done by walking along the index (signified by the type of "range"), making it also constant-time:

mysql> explain select * from items where id >= (select max(id) from items where id < 11) limit 3;
+----+-------------+-------+-------+---------------+---------+---------+------+------+------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                        |
+----+-------------+-------+-------+---------------+---------+---------+------+------+------------------------------+
|  1 | PRIMARY     | items | range | PRIMARY       | PRIMARY | 4       | NULL |    7 | Using where                  | 
|  2 | SUBQUERY    | NULL  | NULL  | NULL          | NULL    | NULL    | NULL | NULL | Select tables optimized away | 
+----+-------------+-------+-------+---------------+---------+---------+------+------+------------------------------+
2 rows in set (0.00 sec)

(I say "constant time"...btree lookups are actually O(logN), but with a *very* high logarithmic base. Also, the entire index is usually in memory, so it's blazingly fast even for millions of rows in the table.)
This account has disabled anonymous posting.
If you don't have an account you can create one now.
HTML doesn't work in the subject.
More info about formatting
 
Notice: This account is set to log the IP addresses of everyone who comments.
Links will be displayed as unclickable URLs to help prevent spam.

Profile

elfs: (Default)
Elf Sternberg

June 2025

S M T W T F S
1234567
891011121314
15161718192021
22232425262728
2930     

Most Popular Tags

Active Entries

Style Credit

Expand Cut Tags

No cut tags
Page generated Jul. 5th, 2025 04:11 pm
Powered by Dreamwidth Studios