问题描述
以下查询需要超过 25 秒才能完成:
The following query is taking more than 25 seconds to complete:
SELECT c.* , (c.age+ (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.newdate))) AS ranking , IF(uc.id_user = 7,1,0) AS favorite
FROM c
LEFT OUTER JOIN uc ON uc.id_cluster = c.id AND uc.id_user = '7'
LEFT OUTER JOIN d ON d.id_cluster = c.id
LEFT OUTER JOIN dt0 ON dt0.id_document = d.id
LEFT OUTER JOIN t0 ON dt0.id_term = t0.id
WHERE MATCH(t0.normalizacion) AGAINST ('term' IN NATURAL LANGUAGE MODE)
GROUP BY c.id
ORDER BY ranking ASC
LIMIT 30
索引:
- c.id 主要
- c.age 索引
- c.newdate 索引
- uc.id_user、uc.id_cluster 主
- d.id 主要
- d.id_cluster 索引
- dt0.id_document, dt0.id_term PRIMARY
- dt0.id_document 索引
- dt0.id_term 索引
- t0.id 主要
- t0.normalization FULLTEXT
如果我删除 ORDER BY 子句，只需 2 秒.
If I remove the ORDER BY clause, it takes only 2 seconds.
我一直在搜索，发现 GROUP BY 和 ORDER BY 必须使用相同的索引(尝试按 c.id 排序，花了 2 或 3 秒).如何将查询更改为更快?
I've been searching and found that the same index has to be used for both GROUP BY and ORDER BY (tried ordering by c.id and took 2 or 3 seconds). How can I change the query to be faster?
推荐答案
(巩固上面的讨论)
您无法根据计算结果进行高效排序.为使此查询快速运行，请创建一个包含 c.age-UNIX_TIMESTAMP(c.newdate) 的 ranking 列.然后在id和ranking上创建索引，即CREATE INDEX id_ranking ON c(id,ranking)使GROUP BY 和 ORDER BY 都编入索引.
You can't order efficiently by a computation result. To make this query work fast, create a ranking column that contains c.age-UNIX_TIMESTAMP(c.newdate). Then create an index on id and ranking, i.e. CREATE INDEX id_ranking ON c (id, ranking) to make both GROUP BY and ORDER BY indexed.
这篇关于当我按计算列 ORDER 时，查询速度显着减慢 - 可以加快速度吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！