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

Missing index on message_working

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 2.3.1
    • Fix Version/s: None
    • Component/s: Messages, Performance
    • Labels:
    • Affected Branches:
      MOODLE_23_STABLE

      Description

      Moodle.org was struggling with this query below which is the messaging query done on login (perhaps even every page request) to check if there are new messages. This has been resolved by db optimisation, but still, explain output shows this query does a full table scan, so we should make sure indexes are in place:

      SELECT count(m.id)
      FROM message m
      JOIN message_working mw ON m.id=mw.unreadmessageid
      JOIN message_processors p ON mw.processorid=p.id
      JOIN user u ON m.useridfrom=u.id
      WHERE m.useridto = '24152'
      AND p.name='popup'AND m.timecreated > '1346845795';
      +-------------+
      | count(m.id) |
      +-------------+
      |           0 |
      +-------------+
      1 row in set (2 min 32.89 sec)
       
      EXPLAIN EXTENDED SELECT count(m.id) FROM message m JOIN message_working mw ON m.id=mw.unreadmessageid JOIN message_processors p ON mw.processorid=p.id JOIN user u ON m.useridfrom=u.id WHERE m.useridto = '24152' AND p.name='popup'AND m.timecreated > '1346845795';
      +----+-------------+-------+--------+-----------------------------+----------+---------+---------------------+--------+----------+--------------------------------+
      | id | select_type | table | type   | possible_keys               | key      | key_len | ref                 | rows   | filtered | Extra                          |
      +----+-------------+-------+--------+-----------------------------+----------+---------+---------------------+--------+----------+--------------------------------+
      |  1 | SIMPLE      | m     | ref    | PRIMARY,useridfrom,useridto | useridto | 8       | const               |      1 |   100.00 | Using where                    |
      |  1 | SIMPLE      | p     | ALL    | PRIMARY                     | NULL     | NULL    | NULL                |      3 |   100.00 | Using where; Using join buffer |
      |  1 | SIMPLE      | mw    | ALL    | NULL                        | NULL     | NULL    | NULL                | 490513 |   100.00 | Using where; Using join buffer |
      |  1 | SIMPLE      | u     | eq_ref | PRIMARY                     | PRIMARY  | 8       | moodle.m.useridfrom |      1 |   100.00 | Using index                    |
      +----+-------------+-------+--------+-----------------------------+----------+---------+---------------------+--------+----------+--------------------------------+
      4 rows in set, 1 warning (0.00 sec)

      Note: I think I modified the query slightly to do a count rather than get data in order to test.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                andyjdavis Andrew Davis
                Reporter:
                poltawski Dan Poltawski
                Participants:
                Component watchers:
                Jake Dallimore, Jun Pataleta, Ryan Wyllie, Matteo Scaramuccia, Jake Dallimore, Jun Pataleta, Ryan Wyllie
              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: