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

Missing index on message_working

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Duplicate
    • 2.3.1
    • None
    • Messages, Performance
    • 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

              andyjdavis Andrew Davis
              poltawski Dan Poltawski
              David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo, Matteo Scaramuccia, David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: