This is the slow query, executed every time /message/index.php?viewing=recentconversations is loaded:
SELECT Concat(message.useridfrom, '-', message.useridto),
otheruser.id,
otheruser.picture,
otheruser.firstname,
otheruser.lastname,
otheruser.firstnamephonetic,
otheruser.lastnamephonetic,
otheruser.middlename,
otheruser.alternatename,
otheruser.imagealt,
otheruser.email,
otheruser.lastaccess,
message.id AS mid,
message.notification,
message.smallmessage,
message.fullmessage,
message.fullmessagehtml,
message.fullmessageformat,
message.timecreated,
contact.id AS contactlistid,
contact.blocked
FROM mdl_message_read message
JOIN (SELECT Max(id) AS messageid,
matchedmessage.useridto,
matchedmessage.useridfrom
FROM mdl_message_read matchedmessage
INNER JOIN (SELECT Max(recentmessages.timecreated)
timecreated,
recentmessages.useridfrom,
recentmessages.useridto
FROM mdl_message_read recentmessages
WHERE ( recentmessages.useridfrom = '2467'
OR recentmessages.useridto = '2467' )
GROUP BY recentmessages.useridfrom,
recentmessages.useridto) recent
ON matchedmessage.useridto = recent.useridto
AND matchedmessage.useridfrom = recent.useridfrom
AND matchedmessage.timecreated =
recent.timecreated
GROUP BY matchedmessage.useridto,
matchedmessage.useridfrom) messagesubset
ON messagesubset.messageid = message.id
JOIN mdl_user otheruser
ON ( message.useridfrom = '2467'
AND message.useridto = otheruser.id )
OR ( message.useridto = '2467'
AND message.useridfrom = otheruser.id )
LEFT JOIN mdl_message_contacts contact
ON contact.userid = '2467'
AND contact.userid = otheruser.id
WHERE otheruser.deleted = 0
AND message.notification = 0
ORDER BY message.timecreated DESC
LIMIT 0, 100 ;
And this is the explain:
+----+-------------+----------------+--------+-----------------------------------------------------+-------------------------+---------+-----------------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+--------+-----------------------------------------------------+-------------------------+---------+-----------------------------------+-------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1810 | Using temporary; Using filesort |
| 1 | PRIMARY | otheruser | range | PRIMARY,mdl_user_del_ix | mdl_user_del_ix | 1 | NULL | 5607 | Using where; Using join buffer |
| 1 | PRIMARY | contact | ref | mdl_messcont_usecon_uix | mdl_messcont_usecon_uix | 8 | const | 3 | |
| 1 | PRIMARY | message | eq_ref | PRIMARY,mdl_messread_use2_ix,mdl_messread_useuse_ix | PRIMARY | 8 | messagesubset.messageid | 1 | Using where |
| 2 | DERIVED | <derived3> | ALL | NULL | NULL | NULL | NULL | 1810 | Using temporary; Using filesort |
| 2 | DERIVED | matchedmessage | ref | mdl_messread_use2_ix,mdl_messread_useuse_ix | mdl_messread_useuse_ix | 16 | recent.useridfrom,recent.useridto | 1 | Using where |
| 3 | DERIVED | recentmessages | index | mdl_messread_use2_ix,mdl_messread_useuse_ix | mdl_messread_useuse_ix | 16 | NULL | 25175 | Using where |
+----+-------------+----------------+--------+-----------------------------------------------------+-------------------------+---------+-----------------------------------+-------+---------------------------------+
7 rows in set (0.05 sec)