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

Add missing Primary/Foreign Key relationships to core Moodle tables

    XMLWordPrintable

Details

    • MOODLE_28_STABLE, MOODLE_400_STABLE
    • MOODLE_401_STABLE
    • MDL-49795-add-missing-keys-indexes-to-core-tables
    • Hide

      As this is a performance related change the main way to test is a before and after. In terms of correctness everything should still be covered by unit tests and generally be a transparent change in terms of funcationality.

      Note: We have not exhaustively demonstrated the performance change between each and every index addition, but for example here is the explain plan for an example using the standard log:

      1) Before the patch is applied run an explain plan on the biggest database you can find with lots of logs

      MariaDB [a_database]> explain SELECT * FROM mdl_logstore_standard_log WHERE realuserid = 2 ORDER BY timecreated DESC, id DESC LIMIT 0, 10000000\G;
      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: mdl_logstore_standard_log
               type: ALL
      possible_keys: NULL
                key: NULL
            key_len: NULL
                ref: NULL
               rows: 7035760
              Extra: Using where; Using filesort
      1 row in set (0.004 sec)
       
      MariaDB [a_database]> SELECT * FROM mdl_logstore_standard_log WHERE realuserid = 2 ORDER BY timecreated DESC, id DESC LIMIT 0, 10000000\G;
      - snip! -
      10047 rows in set (9.630 sec)
       
      MariaDB [a_database]> SELECT * FROM mdl_logstore_standard_log WHERE realuserid = 2 ORDER BY timecreated DESC, id DESC LIMIT 0, 10000000\G;
      - snip! -
      10047 rows in set (14.776 sec)
      

      2) Apply the patch and rerun the explain plan with indexes/foreign-keys, and confirm that is it using the index and also much faster:

      MariaDB [a_database]> explain SELECT * FROM mdl_logstore_standard_log WHERE realuserid = 2 ORDER BY timecreated DESC, id DESC LIMIT 0, 10000000\G;
      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: mdl_logstore_standard_log
               type: ref
      possible_keys: mdl_logsstanlog_rea_ix
                key: mdl_logsstanlog_rea_ix
            key_len: 9
                ref: const
               rows: 18610
              Extra: Using where; Using filesort
      1 row in set (0.000 sec)
       
      MariaDB [a_database]> SELECT * FROM mdl_logstore_standard_log WHERE realuserid = 2 ORDER BY timecreated DESC, id DESC LIMIT 0, 10000000;
      - snip! -
      10047 rows in set (0.091 sec)
       
      MariaDB [a_database]> SELECT * FROM mdl_logstore_standard_log WHERE realuserid = 2 ORDER BY timecreated DESC, id DESC LIMIT 0, 10000000;
      - snip! -
      10047 rows in set (0.103 sec)
      

      Show
      As this is a performance related change the main way to test is a before and after. In terms of correctness everything should still be covered by unit tests and generally be a transparent change in terms of funcationality. Note: We have not exhaustively demonstrated the performance change between each and every index addition, but for example here is the explain plan for an example using the standard log: 1) Before the patch is applied run an explain plan on the biggest database you can find with lots of logs MariaDB [a_database]> explain SELECT * FROM mdl_logstore_standard_log WHERE realuserid = 2 ORDER BY timecreated DESC, id DESC LIMIT 0 , 10000000 \G; *************************** 1 . row *************************** id: 1 select_type: SIMPLE table: mdl_logstore_standard_log type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 7035760 Extra: Using where; Using filesort 1 row in set ( 0.004 sec)   MariaDB [a_database]> SELECT * FROM mdl_logstore_standard_log WHERE realuserid = 2 ORDER BY timecreated DESC, id DESC LIMIT 0 , 10000000 \G; - snip! - 10047 rows in set ( 9.630 sec)   MariaDB [a_database]> SELECT * FROM mdl_logstore_standard_log WHERE realuserid = 2 ORDER BY timecreated DESC, id DESC LIMIT 0 , 10000000 \G; - snip! - 10047 rows in set ( 14.776 sec) 2) Apply the patch and rerun the explain plan with indexes/foreign-keys, and confirm that is it using the index and also much faster: MariaDB [a_database]> explain SELECT * FROM mdl_logstore_standard_log WHERE realuserid = 2 ORDER BY timecreated DESC, id DESC LIMIT 0 , 10000000 \G; *************************** 1 . row *************************** id: 1 select_type: SIMPLE table: mdl_logstore_standard_log type: ref possible_keys: mdl_logsstanlog_rea_ix key: mdl_logsstanlog_rea_ix key_len: 9 ref: const rows: 18610 Extra: Using where; Using filesort 1 row in set ( 0.000 sec)   MariaDB [a_database]> SELECT * FROM mdl_logstore_standard_log WHERE realuserid = 2 ORDER BY timecreated DESC, id DESC LIMIT 0 , 10000000 ; - snip! - 10047 rows in set ( 0.091 sec)   MariaDB [a_database]> SELECT * FROM mdl_logstore_standard_log WHERE realuserid = 2 ORDER BY timecreated DESC, id DESC LIMIT 0 , 10000000 ; - snip! - 10047 rows in set ( 0.103 sec)

    Description

      When a new version of Moodle is released I like to create an ER diagram of the database. I do this using some code to extract the SQL statements from the xml files
      https://github.com/marcusgreen/moodle_local-sqlgenerator

      And then import into MySQLWorkbench which has a "reverse engineer" facility to generate diagrams. These diagrams are dependent on the existence of Primary to Foreign Key relationships and there are places where this works well as can be seen in the Quiz diagram

      http://www.examulator.com/er/components/quiz.png

      However there are places where there appears to be missing relationships. I would like these to be added

      to core moodle to improve documentation and to make it easier for me to generate diagrams. If referential integrity were ever set to be on it would of course help keep the data sensible but that is another issue.

      To start with I have identified missing relationships between the tables course and course_sections and course and course_modules. These could be added to the file
      lib\db\install.xml

      A list of these relationships can be found in the morekeys.xml file at
      https://github.com/marcusgreen/moodle_local-sqlgenerator

      This issue links with
      https://tracker.moodle.org/browse/MDL-30799
      Which is about turning on referantial integrity for developers

      Attachments

        Issue Links

          Activity

            People

              kevinpham Kevin Pham
              marcusgreen Marcus Green
              Nicholas Hoobin Nicholas Hoobin
              Shamim Rezaie Shamim Rezaie
              Angelia Dela Cruz Angelia Dela Cruz
              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:
              9 Vote for this issue
              Watchers:
              19 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                28/Nov/22

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 2 days, 33 minutes
                  2d 33m