gpt4 book ai didi

php - GROUP_CONCAT 太慢

转载 作者:行者123 更新时间:2023-11-30 01:23:14 25 4
gpt4 key购买 nike

您好,我在查询中添加了 GROUP_CONCAT 函数,该函数终止了我的查询:/。我的查询是:

SELECT u.username,a.user_id,a.id,a.text,a.lang as fromLang,b.lang as toLang,GROUP_CONCAT(DISTINCT b.id) AS translation_ids  FROM sentence as a 
INNER JOIN sentence_relationship as sr ON
(sr.sentence_id = a.id)
INNER JOIN sentence as b ON
(b.id = sr.translation_id AND a.id = sr.sentence_id)
INNER JOIN users as u ON
(u.id = a.user_id) GROUP BY a.id LIMIT 10;

该查询有什么问题?

最佳答案

这是您的查询(有些格式):

SELECT u.username, a.user_id, a.id,a.text,a.lang as fromLang, b.lang as toLang,
GROUP_CONCAT(DISTINCT b.id) AS translation_ids
FROM sentence a INNER JOIN
sentence_relationship sr
ON sr.sentence_id = a.id INNER JOIN
sentence b
ON b.id = sr.translation_id AND a.id = sr.sentence_id INNER JOIN
users as u
ON u.id = a.user_id
GROUP BY a.id
LIMIT 10;

从您的问题中不清楚group_concat()是否与group by一起添加。这可能会减慢速度。

limit 10 取前 10 个匹配的 a.id(group by 执行隐式排序)。如果您使用子查询执行此操作,它可能会加快查询速度:

SELECT u.username, a.user_id, a.id,a.text,a.lang as fromLang, b.lang as toLang,
GROUP_CONCAT(DISTINCT b.id) AS translation_ids
FROM (select s.*
from sentence s
order by a.id
limit 10
) a INNER JOIN
sentence_relationship sr
ON sr.sentence_id = a.id INNER JOIN
sentence b
ON b.id = sr.translation_id AND a.id = sr.sentence_id INNER JOIN
users as u
ON u.id = a.user_id
GROUP BY a.id;

这假设所有连接都有效并且匹配记录。如果连接用于过滤,那么您返回的数据可能会少于 10 行。

关于php - GROUP_CONCAT 太慢,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/18303940/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com