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

发布于 2024-09-29 08:06:32 字数 1001 浏览 4 评论 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_id、id、senderid 和 recipientid.

解释返回：

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.

分享到QQ

分享到微博

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

发布评论

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

万水千山粽是情ミ 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?..

回复收藏 0 原文