当我按计算列 ORDER 时，查询速度显着减慢 - 可以加快速度吗? [英] When I ORDER BY a computed column, the query slows significantly - Can this be sped up?

问题描述

以下查询需要超过 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 时，查询速度显着减慢 - 可以加快速度吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！