mysql“分组依据”查询非常慢

发布于 2024-09-29 08:06:32 字数 1001 浏览 3 评论 0原文

我在一个大约有 100k 条记录的表中有这个查询,它运行得很慢(3-4s),当我取出组时它要快得多(小于 0.5s)。我很不知道如何解决这个问题:

SELECT msg.id,
       msg.thread_id,
       msg.senderid,
       msg.recipientid, 
       from_user.username AS from_name,
       to_user.username AS to_name
FROM msgtable AS msg
LEFT JOIN usertable AS from_user ON msg.senderid = from_user.id
LEFT JOIN usertabe AS to_user ON msg.recipientid = to_user.id
GROUP BY msg.thread_id
ORDER BY msg.id desc

msgtable 在 thread_ididsenderidrecipientid.

解释返回:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  msg ALL NULL    NULL    NULL    NULL    162346  Using temporary; Using filesort
1   SIMPLE  from_user   eq_ref  PRIMARY PRIMARY 4   db.msg.senderid 1    
1   SIMPLE  to_user eq_ref  PRIMARY PRIMARY 4   db.msg.recipientid  1

任何想法如何在返回相同结果的同时加快速度(每个线程有多个消息,我想在此查询中每个线程仅返回一条消息)。

提前致谢。

I have this query in a table with about 100k records, it runs quite slow (3-4s), when I take out the group it's much faster (less than 0.5s). I'm quite at loss what to do to fix this:

SELECT msg.id,
       msg.thread_id,
       msg.senderid,
       msg.recipientid, 
       from_user.username AS from_name,
       to_user.username AS to_name
FROM msgtable AS msg
LEFT JOIN usertable AS from_user ON msg.senderid = from_user.id
LEFT JOIN usertabe AS to_user ON msg.recipientid = to_user.id
GROUP BY msg.thread_id
ORDER BY msg.id desc

msgtable has indexes on thread_id, id, senderid and recipientid.

explain returns:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  msg ALL NULL    NULL    NULL    NULL    162346  Using temporary; Using filesort
1   SIMPLE  from_user   eq_ref  PRIMARY PRIMARY 4   db.msg.senderid 1    
1   SIMPLE  to_user eq_ref  PRIMARY PRIMARY 4   db.msg.recipientid  1

Any ideas how to speed this up while returning the same result (there are multiple messages per thread, i want to return only one message per thread in this query).

thanks in advance.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

万水千山粽是情ミ 2024-10-06 08:06:32

试试这个:

select m.thread_id, m.id, m.senderid, m.recipientid, 
       f.username as from_name, t.username as to_name
from msgtable m
join usertable f on m.senderid = f.id
join usertable t on m.recipientid = t.id
where m.id = (select MAX(id) from msgtable where thread_id = m.thread_id)

或者这个:

select m.thread_id, m.id, m.senderid, m.recipientid, 
       (select username from usertable where id = m.senderid) as from_name,
       (select username from usertable where id = m.recipientid) as to_name
from msgtable m
where m.id = (select MAX(id) from msgtable where thread_id = m.thread_id)

为什么用户表保持连接?消息可以缺少发件人或收件人吗?...

try this:

select m.thread_id, m.id, m.senderid, m.recipientid, 
       f.username as from_name, t.username as to_name
from msgtable m
join usertable f on m.senderid = f.id
join usertable t on m.recipientid = t.id
where m.id = (select MAX(id) from msgtable where thread_id = m.thread_id)

Or this:

select m.thread_id, m.id, m.senderid, m.recipientid, 
       (select username from usertable where id = m.senderid) as from_name,
       (select username from usertable where id = m.recipientid) as to_name
from msgtable m
where m.id = (select MAX(id) from msgtable where thread_id = m.thread_id)

Why were the user tables left joined? Can a message be missing a from or to?..

甜心 2024-10-06 08:06:32

最大的问题是 msgtable 上没有可用的索引。 至少 senderidrecipientid 创建索引,它应该有助于提高查询速度,因为它将限制需要的结果数量进行扫描。

The biggest problem is that you have no usable indexes on msgtable. Create an index on at least senderid and recipientid, and it should help the speed of your query, as it will limit the number of results needing to be scanned.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文