how to get the "next row" and "previous row" in a resultset, without selecting the entire resultset?

Question

is there a proper way to do this:

ID | name
 1 | aa
 4 | bb
 6 | dd
 9 | ee

ID is the primary index with auto_increment... missing indexes were DELETEd by SQL so there are some empty spaces

if I navigate in page?ID=4 I want to get the previous row (with ID 1) and the next one (with ID 6) (added:) using the same query

is there a way to do this without selecting/traversing the entire resultset?

thank you in advance!

Answer 1

SELECT * from table where `ID` > 4 ORDER BY `ID` ASC LIMIT 1 /* next */
SELECT * from table where `ID` < 4 ORDER BY `ID` DESC LIMIT 1 /* previous */

Answer 2

I would do something like this (for previous record):

SELECT * FROM table
WHERE id < @id
ORDER BY id DESC
LIMIT 1

That should be enough info for the DB engine to optimize the query.