Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-10990

Try to improve some queries no using indexes...

    XMLWordPrintable

    Details

    • 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
    • Database:
      Any
    • Affected Branches:
      MOODLE_19_STABLE
    • Fixed Branches:
      MOODLE_19_STABLE

      Description

      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).

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              skodak Petr Skoda
              Reporter:
              stronk7 Eloy Lafuente (stronk7)
              Tester:
              Eloy Lafuente (stronk7)
              Participants:
              Component watchers:
              Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                15/May/08