How to get next/previous record in MySQL?

sql

performance

best-practices

join

byNikita Barsukov·Nov 2, 2024

In MySQL, you can fetch the next or previous record by examining id values, and adjusting direction with ORDER BY.

Next record:

-- Is this a star trek reference?
SELECT * FROM my_table WHERE id > current_id ORDER BY id ASC LIMIT 1;

Previous record:

-- Going old school (literally)!
SELECT * FROM my_table WHERE id < current_id ORDER BY id DESC LIMIT 1;

Simply replace my_table and current_id with your table name and active record ID. Ensure the table has a unique identifier (primary key) for consistent ordering.

Next level with subqueries

Extending the quick answer, use subqueries to surgically find the next or previous record, minimizing full table scan:

Next record:

-- I’d say this query MIN the rest!
SELECT * FROM my_table WHERE id = (SELECT min(id) FROM my_table WHERE id > current_id);

Previous record:

-- MAXimum efficiency with one fell swoop
SELECT * FROM my_table WHERE id = (SELECT max(id) FROM my_table WHERE id < current_id);

Likewise, when you're operating on a datetime column:

SELECT * FROM my_table WHERE datetime_col > 'current_datetime' ORDER BY datetime_col ASC LIMIT 1;
SELECT * FROM my_table WHERE datetime_col < 'current_datetime' ORDER BY datetime_col DESC LIMIT 1;

Utilizing min(id) and max(id) in subqueries is a smart "short-cut" to the closest adjacent ID, boosting performance and efficiency.

Tackling edge cases

Journey to the edge (cases) of the SQL universe and back with IFNULL:

-- In space, no one can hear you scream... for more query results.
SELECT * FROM my_table WHERE id = IFNULL((SELECT min(id) FROM my_table WHERE id > current_id), current_id);
SELECT * FROM my_table WHERE id = IFNULL((SELECT max(id) FROM my_table WHERE id < current_id), current_id);

IFNULL saves the day when there isn't a next or previous record, returning the current record instead of an empty void (null).

Fear no limitations: Harness optimization

Following are key considerations to optimize your queries:

Database version compatibility: Ensure to use methods that agree with your specific MySQL version.
Combined SQL statements: Roll over next and previous records with a single database call.
Column optimization: Use indexed or sorted columns for a predictable and quicker navigation.
Limitation in LIMIT: Beware of the wolf in sheep's clothing. Incorrect LIMIT values can lead to unexpected outcomes.

Riding query performance bull

Optimization is trifecta of saving resource consumption, time, and made you look:

Stop full scans: Use indexed columns in WHERE to make MySQL use indexes over costly full table scans.
Strategic filter: Concentrate filters that reduce rows to sort for large data volumes.
Switch fetch strategy: Depending upon your use-case, Cache a set of records and navigate in memory rather than pinging database over and again.

Datetime sort magic

IDs are cool and all, but datetime columns can also serve useful and a handy sorting companion:

-- Time travel, sorted.
SELECT * FROM log_entries WHERE timestamp > current_timestamp ORDER BY timestamp ASC LIMIT 1;
SELECT * FROM log_entries WHERE timestamp < current_timestamp ORDER BY timestamp DESC LIMIT 1;