MySQL中的group_concat性能问题 [英] group_concat performance issue in MySQL
问题描述
我在查询中添加了group_concat
并终止了性能.添加之前和之后的说明计划是相同的,所以我对如何优化它感到困惑.
I added a group_concat
to a query and killed the performance. The explain plans are identical before and after I added it, so I'm confused as to how to optimize this.
这是查询的简化版本:
SELECT @curRow := @curRow + 1 AS row_number,
docID,
docTypeID,
CASE WHEN COUNT(1) > 1
THEN group_concat( makeID )
-- THEN 'multiple makes found'
ELSE MIN(makeID)
END AS makeID,
MIN(desc) AS desc
FROM simplified_mysql_table,
(SELECT @curRow := 0) r
GROUP BY docID, docTypeID,
CASE WHEN docTypeID = 1
THEN 0
ELSE row_number
END;
请注意SELECT
中的CASE
语句. group_concat
会降低性能.如果我注释该行并仅输出"multiple make found",则执行速度非常快.知道是什么原因造成的吗?
Note the CASE
statement in the SELECT
. The group_concat
kills performance. If I comment that line and just output 'multiple makes found' it executes very quickly. Any idea what is causing this?
推荐答案
我们发现了潜在的问题.在此查询的原始非简化版本中,我们有一个DISTINCT,这是完全不必要的,并导致group_concat出现性能问题.我不知道为什么会引起这样的问题,但是删除它可以解决性能问题.谢谢大家的帮助.
We found the underlying problem. In the original non-simplified version of this query we had a DISTINCT, which was completely unnecessary and causing the performance issue with group_concat. I'm not sure why it caused such a problem, but removing it fixed the performance issue. Thanks all for your help.
这篇关于MySQL中的group_concat性能问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!