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屋！