-
Improvement
-
Resolution: Deferred
-
Minor
-
None
-
2.9.2
-
None
-
MySQL
-
MOODLE_29_STABLE
The query from function message_get_recent_conversations performs poorly on MySQL 5.5.44.
$sql = "SELECT $userfields,
|
message.id as mid, message.notification, message.smallmessage, message.fullmessage,
|
message.fullmessagehtml, message.fullmessageformat, message.timecreated,
|
contact.id as contactlistid, contact.blocked
|
|
FROM {message_read} message
|
JOIN {user} otheruser ON otheruser.id = CASE
|
WHEN message.useridto = :userid1 THEN message.useridfrom
|
ELSE message.useridto END
|
LEFT JOIN {message_contacts} contact ON contact.userid = :userid2 AND contact.contactid = otheruser.id
|
|
WHERE otheruser.deleted = 0
|
AND (message.useridto = :userid3 OR message.useridfrom = :userid4)
|
AND message.notification = 0
|
AND NOT EXISTS (
|
SELECT 1
|
FROM {message_read} othermessage
|
WHERE ((othermessage.useridto = :userid5 AND othermessage.useridfrom = otheruser.id) OR
|
(othermessage.useridfrom = :userid6 AND othermessage.useridto = otheruser.id))
|
AND (othermessage.timecreated > message.timecreated OR (
|
othermessage.timecreated = message.timecreated AND othermessage.id > message.id))
|
)
|
|
ORDER BY message.timecreated DESC";
|
It takes less than 2 seconds when run on my mdl_message table with 47375 rows but it takes over 10 seconds when run on mdl_message_read with 85129 rows.
Entry from slow log - notice "Rows_examined: 4183610":
# Query_time: 11.531431 Lock_time: 0.000210 Rows_sent: 100 Rows_examined: 4183610
|
SELECT 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 mdl_user otheruser ON otheruser.id = CASE
|
WHEN message.useridto = '72' THEN message.useridfrom
|
ELSE message.useridto END
|
LEFT JOIN mdl_message_contacts contact ON contact.userid = '72' AND contact.contactid = otheruser.id
|
|
WHERE otheruser.deleted = 0
|
AND (message.useridto = '72' OR message.useridfrom = '72')
|
AND message.notification = 0
|
AND NOT EXISTS (
|
SELECT 1
|
FROM mdl_message_read othermessage
|
WHERE ((othermessage.useridto = '72' AND othermessage.useridfrom = otheruser.id) OR
|
(othermessage.useridfrom = '72' AND othermessage.useridto = otheruser.id))
|
AND (othermessage.timecreated > message.timecreated OR (
|
othermessage.timecreated = message.timecreated AND othermessage.id > message.id))
|
)
|
|
ORDER BY message.timecreated DESC LIMIT 0, 100;
|
My mdl_user table has 27159 rows.
mdl_message_contacts 2015 rows.
The performance greatly depends on the # of rows matched, e.g.;
100 total, Query took 11.6246 seconds
748 total, Query took 136.8964 seconds