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

Inefficient queries during privacy data extracts (on MySQL)

    XMLWordPrintable

Details

    Description

      We were recently fulfilling a subject access request using the Moodle data extraction process. While trying to find out where our issues were we found 6 inefficient queries that are part of Moodle core:

      # Query_time: 23.081249 Lock_time: 0.000406 Rows_sent: 0 Rows_examined: 6515170
      SELECT ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance, ctx.locked AS ctxlocked FROM mdl_context ctx WHERE ctx.id IN (SELECT ctx.id FROM mdl_context ctx JOIN mdl_event e ON (e.eventtype = 'site' AND ctx.contextlevel = '10') OR (e.categoryid = ctx.instanceid AND e.eventtype = 'category' AND ctx.contextlevel = '40') OR (e.courseid = ctx.instanceid AND e.eventtype = 'course' AND ctx.contextlevel = '50') OR (e.courseid = ctx.instanceid AND e.eventtype = 'group' AND ctx.contextlevel = '50') OR (e.userid = ctx.instanceid AND e.eventtype = 'user' AND ctx.contextlevel = '30') WHERE e.userid = '305213' UNION SELECT ctx.id FROM mdl_context ctx JOIN mdl_course_modules cm ON cm.id = ctx.instanceid AND ctx.contextlevel = '70' JOIN mdl_modules m ON m.id = cm.module JOIN mdl_event e ON e.modulename = m.name AND e.courseid = cm.course AND e.instance = cm.instance WHERE e.userid = '305213');
       
      # Query_time: 6.170844 Lock_time: 0.000846 Rows_sent: 0 Rows_examined: 6515170
      SELECT ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance, ctx.locked AS ctxlocked FROM mdl_context ctx JOIN ( SELECT DISTINCT ctx.id FROM mdl_context ctx LEFT JOIN mdl_competency_usercomp uc ON uc.userid = ctx.instanceid AND ctx.contextlevel = '30' LEFT JOIN mdl_competency_evidence e ON e.usercompetencyid = uc.id AND (e.usermodified = '305213' OR e.actionuserid = '305213') LEFT JOIN mdl_competency_userevidence ue ON ue.userid = ctx.instanceid AND ctx.contextlevel = '30' AND ue.usermodified = '305213' LEFT JOIN mdl_competency_userevidencecomp uec ON uec.userevidenceid = ue.id AND uec.usermodified = '305213' WHERE uc.usermodified = '305213' OR uc.reviewerid = '305213' OR e.id IS NOT NULL OR ue.id IS NOT NULL OR uec.id IS NOT NULL) target ON ctx.id = target.id;
       
      # Query_time: 12.748786 Lock_time: 0.000328 Rows_sent: 0 Rows_examined: 0
      SELECT ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance, ctx.locked AS ctxlocked FROM mdl_context ctx JOIN ( SELECT DISTINCT ctx.id FROM mdl_context ctx LEFT JOIN mdl_competency_plan p ON p.userid = ctx.instanceid AND ctx.contextlevel = '30' LEFT JOIN mdl_competency_usercomp uc ON uc.userid = ctx.instanceid AND ctx.contextlevel = '30' AND uc.userid = '305213' LEFT JOIN mdl_competency_userevidence ue ON ue.userid = ctx.instanceid AND ctx.contextlevel = '30' AND ue.userid = '305213' LEFT JOIN mdl_competency_usercompcourse ucc ON ucc.courseid = ctx.instanceid AND ctx.contextlevel = '50' AND ucc.userid = '305213' WHERE p.userid = '305213' OR uc.id IS NOT NULL OR ue.id IS NOT NULL OR ucc.id IS NOT NULL) target ON ctx.id = target.id;
       
      # Query_time: 22.696587 Lock_time: 0.000683 Rows_sent: 0 Rows_examined: 6515170
      SELECT ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance, ctx.locked AS ctxlocked FROM mdl_context ctx JOIN ( SELECT DISTINCT ctx.id FROM mdl_context ctx LEFT JOIN mdl_grade_outcomes_history goh ON goh.loggeduser = '305213' AND ( (goh.courseid > 0 AND goh.courseid = ctx.instanceid AND ctx.contextlevel = '50') OR ((goh.courseid IS NULL OR goh.courseid < 1) AND ctx.id = '1') ) LEFT JOIN mdl_grade_categories_history gch ON gch.loggeduser = '305213' AND ( gch.courseid = ctx.instanceid AND ctx.contextlevel = '50' ) LEFT JOIN mdl_grade_items_history gih ON gih.loggeduser = '305213' AND ( gih.courseid = ctx.instanceid AND ctx.contextlevel = '50' ) LEFT JOIN mdl_scale_history sh ON (sh.userid = '305213' OR sh.loggeduser = '305213') AND ( (sh.courseid > 0 AND sh.courseid = ctx.instanceid AND ctx.contextlevel = '50') OR (sh.courseid = 0 AND ctx.id = '1') ) WHERE goh.id IS NOT NULL OR gch.id IS NOT NULL OR gih.id IS NOT NULL OR sh.id IS NOT NULL) target ON ctx.id = target.id;
       
      # Query_time: 26.677843 Lock_time: 0.000439 Rows_sent: 0 Rows_examined: 13036050
      SELECT ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance, ctx.locked AS ctxlocked FROM mdl_context ctx WHERE ctx.id IN (SELECT c.id FROM mdl_context c INNER JOIN mdl_post p ON p.courseid = c.instanceid AND c.contextlevel = '50' WHERE p.module = 'notes' AND p.usermodified = '305213' UNION SELECT c.id FROM mdl_context c INNER JOIN mdl_post p ON p.courseid = c.instanceid AND c.contextlevel = '50' WHERE p.module = 'notes' AND p.userid = '305213' AND p.publishstate IN ('public','site'));
       
      # Query_time: 185.415307 Lock_time: 0.000333 Rows_sent: 275 Rows_examined: 98092473
      SELECT c.id AS contextid, cm.id AS cmid, qa.* FROM mdl_context c JOIN mdl_course_modules cm ON cm.id = c.instanceid AND c.contextlevel = '70' JOIN mdl_modules m ON m.id = cm.module AND m.name = 'quiz' JOIN mdl_quiz q ON q.id = cm.instance JOIN mdl_quiz_attempts qa ON qa.quiz = q.id JOIN mdl_question_usages rel_qu ON rel_qu.id = qa.uniqueid AND rel_qu.component = 'mod_quiz' JOIN mdl_question_attempts rel_qa ON rel_qa.questionusageid = rel_qu.id JOIN mdl_question_attempt_steps rel_qas ON rel_qas.questionattemptid = rel_qa.id WHERE ( qa.userid = '305213' OR rel_qas.userid = '305213' ) AND qa.preview = 0;
      

      These 6 queries took a total of about 4 minutes 40 seconds on our database, with some refactors we can get the total time to be well under 1 second.

      We are running on MySQL.

      Attachments

        1. image-2023-04-20-12-19-03-817.png
          image-2023-04-20-12-19-03-817.png
          20 kB
        2. image-2023-04-21-15-24-39-217.png
          image-2023-04-21-15-24-39-217.png
          48 kB
        3. query-1-optimised.png
          query-1-optimised.png
          20 kB
        4. query-1-original.png
          query-1-original.png
          19 kB
        5. query-2-optimised.png
          query-2-optimised.png
          63 kB
        6. query-2-original.png
          query-2-original.png
          17 kB
        7. query-3-optimised.png
          query-3-optimised.png
          46 kB
        8. query-3-original.png
          query-3-original.png
          17 kB
        9. query-4-optimised.png
          query-4-optimised.png
          16 kB
        10. query-4-original.png
          query-4-original.png
          15 kB
        11. query-5-optimised.png
          query-5-optimised.png
          15 kB
        12. query-5-original.png
          query-5-original.png
          16 kB
        13. query-6-optimised.png
          query-6-optimised.png
          37 kB
        14. query-6-original.png
          query-6-original.png
          21 kB

        Issue Links

          Activity

            People

              nmagill Neill Magill
              nmagill Neill Magill
              Stefan van der Vyver Stefan van der Vyver
              Jun Pataleta Jun Pataleta
              CiBoT CiBoT
              Votes:
              1 Vote for this issue
              Watchers:
              8 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 - 2 days, 3 hours, 28 minutes
                  2d 3h 28m

                  Clockify

                    Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.