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

Full table scans possible on forum_read table

    XMLWordPrintable

Details

    • MOODLE_310_STABLE, MOODLE_311_STABLE
    • MOODLE_400_STABLE
    • MDL-72236-master
    • Hide

      Before the patch

      1. Login to a Moodle database. You'll need access to run explain statements against similar DELETE statements used by Moodle when cleaning up forum data. Recommended database engine to get the same results as below: MySQL/MariaDB.
      2. Run the following statement and note the explain plan using no key for data deletion (NULL in possible_keys and key columns):

        > EXPLAIN DELETE FROM mdl_forum_read WHERE forumid = '1';
        +------+-------------+----------------+------+---------------+------+---------+------+------+-------------+
        | id   | select_type | table          | type | possible_keys | key  | key_len | ref  | rows | Extra       |
        +------+-------------+----------------+------+---------------+------+---------+------+------+-------------+
        |    1 | SIMPLE      | mdl_forum_read | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
        +------+-------------+----------------+------+---------------+------+---------+------+------+-------------+
        

      3. Run the following statement and note the explain plan using no key for data deletion (NULL in possible_keys and key columns):

        > EXPLAIN DELETE FROM mdl_forum_read WHERE discussionid = '1';
        +------+-------------+----------------+------+---------------+------+---------+------+------+-------------+
        | id   | select_type | table          | type | possible_keys | key  | key_len | ref  | rows | Extra       |
        +------+-------------+----------------+------+---------------+------+---------+------+------+-------------+
        |    1 | SIMPLE      | mdl_forum_read | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
        +------+-------------+----------------+------+---------------+------+---------+------+------+-------------+
        

      4. Run the following statement and note the explain plan using a key for data deletion:

        > EXPLAIN DELETE FROM mdl_forum_read WHERE userid = '1';
        +------+-------------+----------------+-------+-----------------------------------------------+------------------------+---------+------+------+-------------+
        | id   | select_type | table          | type  | possible_keys                                 | key                    | key_len | ref  | rows | Extra       |
        +------+-------------+----------------+-------+-----------------------------------------------+------------------------+---------+------+------+-------------+
        |    1 | SIMPLE      | mdl_forum_read | range | mdl_foruread_usefor_ix,mdl_foruread_usedis_ix | mdl_foruread_usefor_ix | 8       | NULL |    1 | Using where |
        +------+-------------+----------------+-------+-----------------------------------------------+------------------------+---------+------+------+-------------+
        

      After the patch

      1. With the patch applied, we can now evaluate the delete statements to ensure the database is using the index for its execution plan.
      2. Run the following statement and check the explain plan now is using a key for data deletion (the possible_keys and key columns are not NULL):

        > EXPLAIN DELETE FROM mdl_forum_read WHERE forumid = '1';
        +------+-------------+----------------+-------+------------------------+------------------------+---------+------+------+-------------+
        | id   | select_type | table          | type  | possible_keys          | key                    | key_len | ref  | rows | Extra       |
        +------+-------------+----------------+-------+------------------------+------------------------+---------+------+------+-------------+
        |    1 | SIMPLE      | mdl_forum_read | range | mdl_foruread_foruse_ix | mdl_foruread_foruse_ix | 8       | NULL |    1 | Using where |
        +------+-------------+----------------+-------+------------------------+------------------------+---------+------+------+-------------+

      3. Run the following statement and check the explain plan now is using a key for data deletion (the possible_keys and key columns are not NULL):

        > EXPLAIN DELETE FROM mdl_forum_read WHERE discussionid = '1';
        +------+-------------+----------------+-------+------------------------+------------------------+---------+------+------+-------------+
        | id   | select_type | table          | type  | possible_keys          | key                    | key_len | ref  | rows | Extra       |
        +------+-------------+----------------+-------+------------------------+------------------------+---------+------+------+-------------+
        |    1 | SIMPLE      | mdl_forum_read | range | mdl_foruread_disuse_ix | mdl_foruread_disuse_ix | 8       | NULL |    1 | Using where |
        +------+-------------+----------------+-------+------------------------+------------------------+---------+------+------+-------------+

      4. Run the following statement and check the explain plan now is using a new key for data deletion (mdl_foruread_use_ix instead of mdl_foruread_usefor_ix):

        > EXPLAIN DELETE FROM mdl_forum_read WHERE userid = '1';
        +------+-------------+----------------+-------+---------------------+---------------------+---------+------+------+-------------+
        | id   | select_type | table          | type  | possible_keys       | key                 | key_len | ref  | rows | Extra       |
        +------+-------------+----------------+-------+---------------------+---------------------+---------+------+------+-------------+
        |    1 | SIMPLE      | mdl_forum_read | range | mdl_foruread_use_ix | mdl_foruread_use_ix | 8       | NULL |    1 | Using where |
        +------+-------------+----------------+-------+---------------------+---------------------+---------+------+------+-------------+
        
        

      Show
      Before the patch Login to a Moodle database. You'll need access to run explain statements against similar DELETE statements used by Moodle when cleaning up forum data. Recommended database engine to get the same results as below: MySQL/MariaDB. Run the following statement and note the explain plan using no key for data deletion (NULL in possible_keys and key columns): > EXPLAIN DELETE FROM mdl_forum_read WHERE forumid = '1' ; +------+-------------+----------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | mdl_forum_read | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +------+-------------+----------------+------+---------------+------+---------+------+------+-------------+ Run the following statement and note the explain plan using no key for data deletion (NULL in possible_keys and key columns): > EXPLAIN DELETE FROM mdl_forum_read WHERE discussionid = '1' ; +------+-------------+----------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | mdl_forum_read | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +------+-------------+----------------+------+---------------+------+---------+------+------+-------------+ Run the following statement and note the explain plan using a key for data deletion: > EXPLAIN DELETE FROM mdl_forum_read WHERE userid = '1' ; +------+-------------+----------------+-------+-----------------------------------------------+------------------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------------+-------+-----------------------------------------------+------------------------+---------+------+------+-------------+ | 1 | SIMPLE | mdl_forum_read | range | mdl_foruread_usefor_ix,mdl_foruread_usedis_ix | mdl_foruread_usefor_ix | 8 | NULL | 1 | Using where | +------+-------------+----------------+-------+-----------------------------------------------+------------------------+---------+------+------+-------------+ After the patch With the patch applied, we can now evaluate the delete statements to ensure the database is using the index for its execution plan. Run the following statement and check the explain plan now is using a key for data deletion (the possible_keys and key columns are not NULL): > EXPLAIN DELETE FROM mdl_forum_read WHERE forumid = '1' ; +------+-------------+----------------+-------+------------------------+------------------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------------+-------+------------------------+------------------------+---------+------+------+-------------+ | 1 | SIMPLE | mdl_forum_read | range | mdl_foruread_foruse_ix | mdl_foruread_foruse_ix | 8 | NULL | 1 | Using where | +------+-------------+----------------+-------+------------------------+------------------------+---------+------+------+-------------+ Run the following statement and check the explain plan now is using a key for data deletion (the possible_keys and key columns are not NULL): > EXPLAIN DELETE FROM mdl_forum_read WHERE discussionid = '1' ; +------+-------------+----------------+-------+------------------------+------------------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------------+-------+------------------------+------------------------+---------+------+------+-------------+ | 1 | SIMPLE | mdl_forum_read | range | mdl_foruread_disuse_ix | mdl_foruread_disuse_ix | 8 | NULL | 1 | Using where | +------+-------------+----------------+-------+------------------------+------------------------+---------+------+------+-------------+ Run the following statement and check the explain plan now is using a new key for data deletion (mdl_foruread_use_ix instead of mdl_foruread_usefor_ix): > EXPLAIN DELETE FROM mdl_forum_read WHERE userid = '1' ; +------+-------------+----------------+-------+---------------------+---------------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------------+-------+---------------------+---------------------+---------+------+------+-------------+ | 1 | SIMPLE | mdl_forum_read | range | mdl_foruread_use_ix | mdl_foruread_use_ix | 8 | NULL | 1 | Using where | +------+-------------+----------------+-------+---------------------+---------------------+---------+------+------+-------------+

    Description

      Our DBA has noticed that there have been slow queries caused by full table scans on the forum_read table, for example:

      SQL > explain DELETE FROM mdl_forum_read WHERE forumid = '215437';
      +----+-------------+----------------+------------+------+---------------+------+---------+------+----------+----------+-------------+
      | id | select_type | table          | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
      +----+-------------+----------------+------------+------+---------------+------+---------+------+----------+----------+-------------+
      |  1 | DELETE      | mdl_forum_read | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 15352769 |      100 | Using where |
      +----+-------------+----------------+------------+------+---------------+------+---------+------+----------+----------+-------------+
      

      Note that it is scanning over 15 million records.

      The table currently has the following indexes:

      • userid, forumid
      • userid, discussionid
      • postid, userid

      This means queries using forumid and discussionid without also using userid will not be able to use an index resulting in a full table scan.

      Use of just forumid happens in activities like:

      The forum_tp_delete_read_records has usages that mean that disucssionid, forumid, userid and postid are all used alone.

      This makes me think that the optimum indexes for this table are:

      1. forumid, userid (reversed)
      2. discussionid, userid (reversed)
      3. postid, userid (same as now)
      4. userid (new index)

      Attachments

        Activity

          People

            inkjet2000 Justin Merrill
            nmagill Neill Magill
            Neill Magill Neill Magill
            Sara Arjona (@sarjona) Sara Arjona (@sarjona)
            Gladys Basiana Gladys Basiana
            Andrew Lyons, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
            Votes:
            15 Vote for this issue
            Watchers:
            13 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Time Tracking

                Estimated:
                Original Estimate - 0 minutes
                0m
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 1 hour, 26 minutes
                1h 26m