[3.9] Slow query?

[3.9] Slow query?

by Richard van Iwaarden -
Number of replies: 10
Picture of Particularly helpful Moodlers

This query takes about 2.7 seconds, which is too long I think:

SELECT m.*, mcm.userid as useridto
                  FROM mdl_messages m
            INNER JOIN mdl_message_conversations mc
                    ON m.conversationid = mc.id
            INNER JOIN mdl_message_conversation_members mcm
                    ON mcm.conversationid = mc.id
                 WHERE mcm.userid != m.useridfrom
                   AND mcm.userid != '-10' AND mcm.userid != '-20' AND m.timecreated >= '1599197406'
              ORDER BY m.timecreated ASC;
 
While simplifying the query the following difference astonished me:
 
This query takes about 2.5 seconds:
SELECT useridfrom FROM mdl_messages WHERE timecreated >= 1599197406 LIMIT 1
This query takes about 0.1 seconds:
SELECT id FROM mdl_messages WHERE timecreated >= 1599197406 LIMIT 1
 
The mdl_messages table consists of about 300000 rows and is in the innodb format.

Average of ratings: -
In reply to Richard van Iwaarden

Re: [3.9] Slow query?

by Dominique Palumbo -
Picture of Particularly helpful Moodlers Picture of Plugin developers
Hi,

I've notice the same problem on Moodle 3.7 but we've more than a million of record.

But to speed up I've reduce the number of fields returned. Because it seems the problem is more the weight of the fields than the number of records.

So I try somehing like that on table mdl_messages before making links with the others.

SELECT id, conversationid, subject FROM mdl_messages WHERE timecreated >= '1560197406'

Hope it's help.

But in fact it's surprisly slow but the size of the returned data are probably huge.

Dominique.
Average of ratings: -
In reply to Dominique Palumbo

Re: [3.9] Slow query?

by Richard van Iwaarden -
Picture of Particularly helpful Moodlers
Thanks Dominique!

Actually we have noticed that Moodle is doing the mentioned query itself, we are not doing this custom query. So if we would like to edit this query, how do we go about that?
Average of ratings: -
In reply to Richard van Iwaarden

Re: [3.9] Slow query?

by Dominique Palumbo -
Picture of Particularly helpful Moodlers Picture of Plugin developers
Hi,

the best it's probably to add a request and propose the solution (if possible) to the Moodle Tracker.

I've looking in the code most of the place where it's used they've the id in the where clause so these one are fast.

Moodle 3.7.1

\message\classes\search\base_message.php -> Seems to be the place where it has to be optimized ?
(if you know in the UI where this called, I can give some try to optimize it)

    Line 188:         $sql = "SELECT m.*, mcm.userid as useridto
\message\classes\search\message_received.php -> OK id in the where clause
    Line 73:         $sql = "SELECT m.*, mcm.userid as useridto
\message\classes\search\message_sent.php -> OK id in the where clause
    Line 72:         $sql = "SELECT m.*, mcm.userid as useridto
\message\externallib.php -> OK id in the where clause
    Line 3542:         $sql = "SELECT m.*, mcm.userid as useridto
\message\tests\externallib_test.php -> for testing...
    Line 141:         $sql = "SELECT m.*, mcm.userid as useridto
    Line 262:         $sql = "SELECT m.*, mcm.userid as useridto

Average of ratings: -
In reply to Dominique Palumbo

Re: [3.9] Slow query?

by Richard van Iwaarden -
Picture of Particularly helpful Moodlers
Hi Dominique,
This is to much tech for me. Could you formulate this to the best of your abilities in a Moodle tracker and post the ID here? I will vote for it immediately.

Hope you can do this for me smile

Thanks!
Average of ratings: -
In reply to Richard van Iwaarden

Re: [3.9] Slow query?

by Dominique Palumbo -
Picture of Particularly helpful Moodlers Picture of Plugin developers
Hi Richard,

I would like to know where it's use in the Moodle interface to see if an optimisation can be done before asking for it.
Or at least propose something feasible to improve it.

Thanks.

Dominique.
Average of ratings: -
In reply to Dominique Palumbo

Re: [3.9] Slow query?

by Richard van Iwaarden -
Picture of Particularly helpful Moodlers
I understand, and I don't know. We found this query when we turned on the SQL logging to see which queries take a long time. The query above was one of them. That's all I know. I don't even know where the query comes from.
Average of ratings: -
In reply to Dominique Palumbo

Re: [3.9] Slow query?

by Vitaly Potenko -
Picture of Core developers Picture of Plugin developers
I'm pretty sure the SQL Richard posted is generated in the 'message_get_messages()' function in message/lib.php.
Average of ratings: -
In reply to Vitaly Potenko

Re: [3.9] Slow query?

by Dominique Palumbo -
Picture of Particularly helpful Moodlers Picture of Plugin developers
Hi,

Thanks Vitaly, I'll watch it.

@Richard, did you launch the phpunit test of Moodle ?
Average of ratings: -