Moodle

• Type: Improvement
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 1.9
• Fix Version/s: 1.9.1
• Component/s: General
• Labels:

  None
• Environment:

  moodle.org, mysql

after enabling slow queries logging and no using indexes logging at moodle.org, we have detected some queries that are executed dozens of times per minute and aren't using indexes at all. Here it's the list:

1) SELECT id FROM user WHERE username = 'guest' LIMIT 1;
(this is the more frequent with difference - should include mnethostid to use the correct index)

2) SELECT * FROM event WHERE (timestart >= 1187971200 OR timestart + timeduration > 1187971200) AND timestart <= 1190390399 AND ( (userid = 208654 AND courseid = 0 AND groupid = 0) OR (groupid = 0 AND courseid IN (11,1))) AND visible = 1 ORDER BY timestart;
(complex to fix because it includes a lot of "OR" clauses and <> inequalities - perhaps one composite index by all the fields in the WHERE clause could help, not really sure)

3) SELECT
h.id AS id,
h.name AS name,
COUNT AS count_attempts
FROM
hotpot h,
hotpot_attempts a
WHERE
h.course = 34
AND h.id = a.hotpot
AND a.id = a.clickreportid
AND a.starttime > 1187805492
GROUP BY
h.id, h.name;
(not really often but happens)

4) SELECT dc.id AS contentid, dr.id AS recordid, dc.content AS content, d.id AS dataid FROM data d, data_fields df, data_records dr, data_content dc WHERE (d.course = '23' or d.course = '1')AND d.id = df.dataid AND df.id = dc.fieldid AND d.id = dr.dataid AND dr.id = dc.recordid AND df.type = 'text' AND df.param1 = 1;
(not really often but happens)

5) When executing cron, I've seen thousands of these executed by minute:
SELECT rc.capability, c1.id as id1, c1.id as id2, (c1.contextlevel * 100) AS aggrlevel,
SUM(rc.permission) AS sum
FROM
role_assignments ra,
role_capabilities rc,
context c1
WHERE
ra.contextid=c1.id AND
ra.roleid=rc.roleid AND
ra.userid=82749 AND
c1.id IN (74,63,50,224) AND
rc.contextid=50
AND (rc.capability = 'mod/forum:viewdiscussion' OR rc.capability = 'moodle/site:doanything')
GROUP BY
rc.capability, c1.id, c1.contextlevel * 100
HAVING
SUM(rc.permission) != 0

UNION ALL

SELECT rc.capability, c1.id as id1, c2.id as id2, (c1.contextlevel * 100 + c2.contextlevel) AS aggrlevel,
SUM(rc.permission) AS sum
FROM
role_assignments ra INNER JOIN
role_capabilities rc on ra.roleid = rc.roleid INNER JOIN
context c1 on ra.contextid = c1.id INNER JOIN
context c2 on rc.contextid = c2.id INNER JOIN
context_rel cr on cr.c1 = c2.id AND cr.c2 = c1.id
WHERE
ra.userid=82749 AND
c1.id IN (74,63,50,224) AND
rc.contextid != 50
AND (rc.capability = 'mod/forum:viewdiscussion' OR rc.capability = 'moodle/site:doanything')
GROUP BY
rc.capability, c1.id, c2.id, c1.contextlevel * 100 + c2.contextlevel
HAVING
SUM(rc.permission) != 0
ORDER BY
aggrlevel ASC;
(I guess it's normal but the KEY here is that, for each user there are TWO queries like the above, one to check for the "viewdiscussion" capability and another one to check for the "replypost" capability. Just wondering if they could be reduced to one or no).

IMPORTANT NOTE: Due to the MySQL cache some of these queries (specially 1 & 2) doesn't have impact at moodle.org (in fact all them are performed in 0 secs. But some other DBs could suffer with them so we should fix as many as possible of them.

Ciao

P.S.: I've stopped the logging of un-indexed queries (because it grows really quick). Right now only slow queries (> 1 sec remains active).

• Assignee:

  Petr Škoda (skodak)

  Reporter:

  Eloy Lafuente (stronk7)

  Tester:

  Eloy Lafuente (stronk7)

  Participants:

  Eloy Lafuente (stronk7), Martin Dougiamas, Petr Škoda (skodak)

• Created:

  25/Aug/07 2:16 AM

  Updated:

  24/Apr/08 9:52 AM

  Resolved:

  19/Mar/08 2:08 AM