mysql“分组依据”查询非常慢
我在一个大约有 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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
试试这个:
或者这个:
为什么用户表保持连接?消息可以缺少发件人或收件人吗?...
try this:
Or this:
Why were the user tables left joined? Can a message be missing a from or to?..
最大的问题是
msgtable
上没有可用的索引。 至少senderid
和recipientid
创建索引,它应该有助于提高查询速度,因为它将限制需要的结果数量进行扫描。The biggest problem is that you have no usable indexes on
msgtable
. Create an index on at leastsenderid
andrecipientid
, and it should help the speed of your query, as it will limit the number of results needing to be scanned.