What's the fastest way of find the previous and next record in a result not sorted by ID in MySQL?

Question

I've looked around and the only questions/answers I can find on the site are related to sorting by the ID in MySQL. Questions such as: stackoverflow.com/questions/645102 and stackoverflow.com/questions/169233

I am instead looking to find the previous and next rows when sorting by another column other than ID. For example:

I first run this query to get a list of users:

SELECT id, first_name, last_name 
FROM user 
WHERE last_name LIKE 'm%' 
    AND active_flag = 1 
ORDER BY last_name, first_name

Now a user clicks on one of the results and they are sent to a detail page for that user. On that page I want to display previous and next links.

One idea I had was to do the same query, but limit it for 3 rows, 1 before, the current one and one after, but then I realized I don't know where in the list of results the current user is.

Any other ideas other than select the entire result set, getting the key of the array and then find the previous and next ones?

I am using MySQL and PHP.

Answer 1

The "less than" and "greater than" operators works on string fields as well. Fetch the current record. Then do:

SELECT id, first_name, last_name 
FROM user 
WHERE last_name >= $current_lastname
    AND first_name > $current_firstname
    AND active_flag = 1 
ORDER BY last_name, first_name

This query should be fast if you have a clustered index on (last_name, first_name). Of course, there's no guarantees when using MySQL, so you'll have to run EXPLAIN on the query to make sure.