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

mod_book : add bookid index to book_chapters

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide

      Instructions are provided for testing in PostgreSQL or MySQL, but you only need to perform the test in one of them.

      Before applying the patch

      1. Download the file "MDL-69572_book_generator.php", and place it in the root folder (same folder of config.php)
      2. Run the script to generate courses and books/bookchapters (it takes quite a while)

        php MDL-69572_book_generator.php
        

      3. Go to a course
      4. Go to a book in the course, note the book id from the URL (from now on, the_book_id)
      5. Turn on DB timing:
        • PostgreSQL: \timing;
        • MySQL: set profiling = 1;
      6. Run this query:

        select * from mdl_book_chapters where bookid = the_book_id;
        

        • PostgreSQL: note the time.
        • MySQL:
          • Run show profiles;
          • Note the time of the previously executed query (last row).
      7. Run this query:

        explain select * from mdl_book_chapters where bookid = the_book_id;
        

        • PostgreSQL:
          • Note the cost
          • Confirm that the scan is "Seq Scan"
        • MySQL: Confirm the value of the columns possible_keys and key is NULL (no index used).

      After applying the patch

      1. Upgrade the Moodle instance
      2. Confirm the index was added to mdl_book_chapters table:
        • PostgreSQL: \d mdl_book_chapters;
        • MySQL: show index from mdl_book_chapters;
      3. Run this query:

        select * from mdl_book_chapters where bookid = the_book_id;
        

        • PostgreSQL: Confirm the time taken is much smaller than it took when running without the index.
        • MySQL:
          • Run show profiles;
          • Confirm the time taken is much smaller than it took when running without the index
      4. Run this query:

        explain select * from mdl_book_chapters where bookid = the_book_id;
        

        • PostgreSQL: Confirm the cost for running the query with the new index should be smaller than the one without the index
          • Confirm that the scan is "Index Scan" and it uses the new index
        • MySQL: Confirm the value of the columns possible_keys and key is something like "mdl_bookchap_boo_ix"
      Show
      Instructions are provided for testing in PostgreSQL or MySQL, but you only need to perform the test in one of them. Before applying the patch Download the file " MDL-69572 _book_generator.php", and place it in the root folder (same folder of config.php) Run the script to generate courses and books/bookchapters (it takes quite a while) php MDL-69572_book_generator.php Go to a course Go to a book in the course, note the book id from the URL (from now on, the_book_id) Turn on DB timing: PostgreSQL : \timing; MySQL : set profiling = 1; Run this query: select * from mdl_book_chapters where bookid = the_book_id; PostgreSQL : note the time. MySQL : Run  show profiles; Note the time of the previously executed query (last row). Run this query: explain select * from mdl_book_chapters where bookid = the_book_id; PostgreSQL : Note the cost Confirm that the scan is "Seq Scan" MySQL : Confirm the value of the columns possible_keys and key is NULL (no index used). After applying the patch Upgrade the Moodle instance Confirm the index was added to mdl_book_chapters table: PostgreSQL : \d mdl_book_chapters; MySQL : show index from mdl_book_chapters; Run this query: select * from mdl_book_chapters where bookid = the_book_id; PostgreSQL : Confirm the time taken is much smaller than it took when running without the index. MySQL : Run  show profiles; Confirm the time taken is much smaller than it took when running without the index Run this query: explain select * from mdl_book_chapters where bookid = the_book_id; PostgreSQL : Confirm the cost for running the query with the new index should be smaller than the one without the index Confirm that the scan is "Index Scan" and it uses the new index MySQL : Confirm the value of the columns possible_keys and key is something like "mdl_bookchap_boo_ix"
    • Affected Branches:
      MOODLE_39_STABLE
    • Fixed Branches:
      MOODLE_310_STABLE
    • Pull Master Branch:
      MDL-69572-Master

      Description

      There are several queries on book_chapters using bookingid.

      Adding bookingid index on the table will help to improve the performance.

        Attachments

          Activity

            People

            Assignee:
            Nguyen Nathan Nguyen
            Reporter:
            Nguyen Nathan Nguyen
            Peer reviewer:
            Peter Dias
            Integrator:
            Víctor Déniz Falcón
            Tester:
            Janelle Barcega
            Participants:
            Component watchers:
            David Jones, Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias, Sujith Haridasan
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:
              Fix Release Date:
              9/Nov/20

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 5 hours, 50 minutes
                5h 50m