在MySQL中没有按ID排序的结果中找到上一条和下一条记录的最快方法是什么？

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: *.com/questions/645102 and *.com/questions/169233

我环顾四周,我在网站上找到的唯一问题/答案与MySQL中的ID排序有关。问题如:*.com/questions/645102和*.com/questions/169233

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

相反,当我按ID以外的其他列进行排序时,我希望找到上一行和下一行。例如:

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.

我的一个想法是做同样的查询,但限制它为3行,1之前,当前的1和1之后,但后来我意识到我不知道当前用户的结果列表中的位置。

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.

我正在使用MySQL和PHP。

1 个解决方案

#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.

如果在(last_name,first_name)上有聚簇索引,则此查询应该很快。当然,使用MySQL时无法保证,因此您必须在查询上运行EXPLAIN才能确保。

#1