-
Improvement
-
Resolution: Unresolved
-
Minor
-
None
-
4.1.19
-
MOODLE_401_STABLE
-
main
-
Moderate
-
-
Fails against automated checks. 
Checked MDL-85445 using repository: https://github.com/tmas0/moodle
Built on: Mon May 12 12:16:23 UTC 2025
Fails against automated checks. Checked MDL-85445 using repository: https://github.com/tmas0/moodle main (1 errors / 2 warnings) [branch: main | CI Job ] overview (0/0) , phplint (0/0) , phpcs (0/0) , js (0/0) , css (0/0) , phpdoc (0/0) , commit (1/2) , savepoint (0/0) , thirdparty (0/0) , externalbackup (0/0) , grunt (0/0) , shifter (0/0) , mustache (0/0) , gherkin (0/0) , Should these errors be fixed? Built on: Mon May 12 12:16:23 UTC 2025
main (1 errors / 2 warnings) [branch: Hi,
In order to improve the performance of the messaging system when it's heavily used, I have found that the following query have a very bad performance when the messages table have more than 500K messages.
$sql = "SELECT m.id as messageid, mc.id as id, mc.name as conversationname, mc.type as conversationtype, m.useridfrom,
|
m.smallmessage, m.fullmessage, m.fullmessageformat, m.fullmessagetrust, m.fullmessagehtml, m.timecreated,
|
mc.component, mc.itemtype, mc.itemid, mc.contextid, mca.action as ismuted
|
FROM {message_conversations} mc
|
INNER JOIN {message_conversation_members} mcm
|
ON (mcm.conversationid = mc.id AND mcm.userid = :userid3)
|
LEFT JOIN (
|
SELECT m.conversationid, MAX(m.id) AS messageid
|
FROM {messages} m
|
INNER JOIN (
|
SELECT m.conversationid, MAX(m.timecreated) as maxtime
|
FROM {messages} m
|
INNER JOIN {message_conversation_members} mcm
|
ON mcm.conversationid = m.conversationid
|
LEFT JOIN {message_user_actions} mua
|
ON (mua.messageid = m.id AND mua.userid = :userid AND mua.action = :action)
|
WHERE mua.id is NULL
|
AND mcm.userid = :userid2
|
GROUP BY m.conversationid
|
) maxmessage
|
ON maxmessage.maxtime = m.timecreated AND maxmessage.conversationid = m.conversationid
|
GROUP BY m.conversationid
|
) lastmessage
|
ON lastmessage.conversationid = mc.id
|
LEFT JOIN {messages} m
|
ON m.id = lastmessage.messageid
|
LEFT JOIN {message_conversation_actions} mca
|
ON (mca.conversationid = mc.id AND mca.userid = :userid4 AND mca.action = :convaction)
|
WHERE mc.id IS NOT NULL
|
AND mc.enabled = 1 $typesql $favouritesql |
ORDER BY (CASE WHEN m.timecreated IS NULL THEN 0 ELSE 1 END) DESC, m.timecreated DESC, id DESC"; |
$params = array_merge($favouriteparams,
|
$typeparams, ['userid' => $userid, 'action' => |
self::MESSAGE_ACTION_DELETED,
|
'userid2' => $userid, |
'userid3' => $userid, 'userid4' => $userid, 'convaction' => |
self::CONVERSATION_ACTION_MUTED]);
|
$conversationset = $DB->get_recordset_sql($sql, $params, $limitfrom, $limitnum);
|
Analysing this query, on the following code fragment appears the problem:
SELECT m.conversationid, MAX(m.timecreated) as maxtime
|
FROM {messages} m
|
INNER JOIN {message_conversation_members} mcm
|
ON mcm.conversationid = m.conversationid
|
LEFT JOIN {message_user_actions} mua
|
ON (mua.messageid = m.id AND mua.userid = :userid AND mua.action = :action)
|
WHERE mua.id is NULL
|
AND mcm.userid = :userid1
|
GROUP BY m.conversationid
|
The LEFT JOIN using mua.id IS NULL means that you need to get all the messages that have not been DELETED.
My proposal is change this LEFT JOIN with mua.IS NULL for a WHERE IF EXISTS to avoid all DELETED messages. With this change, it looks like:
SELECT m.conversationid, MAX(m.timecreated) as maxtime
|
FROM {messages} m
|
INNER JOIN {message_conversation_members} mcm
|
ON mcm.conversationid = m.conversationid AND mcm.userid = :userid1
|
WHERE NOT EXISTS (
|
SELECT 1 |
FROM {message_user_actions} mua
|
WHERE mua.messageid = m.id
|
AND mua.userid = :userid
|
AND mua.action = :action
|
)
|
GROUP BY m.conversationid
|
The changes are:
- Drop LEFT and change for WHERE NOT EXISTS
- Move mcm.userid to ON clause.
Regards