在MySQL中优化ORDER BY LIMIT查询

问题描述：

在我的Web应用程序中，我制作了一个内部消息传递系统。我想在每个页面上放置一个'上一个'和'下一个'链接（用户在查看邮件的地方）。在MySQL中优化ORDER BY LIMIT查询

为了获得下一个和以前的ID我执行两个查询：

对于前一个：

SELECT id FROM pages WHERE (id<$requestedPageId) ORDER BY id DESC LIMIT 1

并为下一个：

SELECT id FROM pages WHERE (id>$requestedPageId) ORDER BY id LIMIT 1

EXPLAIN说查询类型是“范围”，行列表示它将检查所有行小于或大于页面的id（大数字）。 Extra行说“使用where”。

看来MySQL忽略了我只需要一行。 MySQL是否足够聪明，可以优化这种查询，这样它就可以找到页面的行并搜索第一个匹配的行。

有没有更好的方法来获取下一页和上一页的ID？

其他注意事项：

这个问题似乎在每一个ORDER BY限制类型的查询存在（例如：当我分裂一长串多页。）。
Where子句不是这么简单（我想让用户访问他有权访问的下一个/上一个页面，尽管没有连接）
所有列出现在WHERE索引（id是主键）
变量被保护以防止注入。

EDIT1：

所以我目前使用的查询：

SELECT id 
FROM reports 
WHERE (id<$requestedPageId) AND ((isPublic=1) OR (recipientId=$recipient)) 
ORDER BY id DESC 
LIMIT 1

或当我重新因素它作为回答说：

SELECT MAX(id) 
FROM reports 
WHERE (id<$requestedPageId) AND ((isPublic=1) OR (recipientId=$recipient))

答

对于前面

SELECT MAX(id) FROM pages WHERE id<$requestPageId

并且接下来

SELECT MIN(id) FROM pages WHERE id>$requestedPageId

它优化了这个简单情况下的表格。但只要我在where子句中添加一个附加条件，它就会检查所有行。（EXPLAIN类型将全部）。我要编辑我的原始文章，并将其放入我正在使用的查询中。 – Calmarius 2011-03-05 12:19:35

您是否在isPublic和recipientId上设置了索引？ – 2011-03-05 13:20:52

答

数据库的行为与预期相同。您的查询是一个范围查询，因为符号较少（编号为< $ requestedPageId）。 OR语句使得使用单个索引查找结果变得更加困难。而排序结果意味着它必须得到所有匹配的行才能执行排序，即使你只需要1行。

您无法将其作为“常量”类型查询，但您可以使用索引，子查询和/或联合语句对其进行优化。

下面是一个查询来统治他们。我并不是说这是最好的解决方案，而只是解决问题的一种方法。首先，如果您创建两个索引，一个用于recipientId，另一个用于isPublic，则此查询效果更好。

SELECT 
GREATEST(
    (SELECT MAX(id) FROM reports 
    WHERE id < $requestedPageId AND recipientId = $recipient), 
    (SELECT MAX(id) FROM reports 
    WHERE id < $requestedPageId AND isPublic = 1) 
) AS prev_id 
LEAST(
    (SELECT MIN(id) FROM reports 
    WHERE id > $requestedPageId AND recipientId = $recipient), 
    (SELECT MIN(id) FROM reports 
    WHERE id > $requestedPageId AND isPublic = 1) 
) AS next_id

在MySQL中优化ORDER BY LIMIT查询

相关推荐