mysql "group by" very slow query.

Question

hi all, 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.

Answer 1

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?..

Answer 2

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.