Uploaded image for project: 'Sakai'
  1. Sakai
  2. SAK-41878

ASN_SUBMISSION_SUBMITTER needs an index on SUBMITTER

    XMLWordPrintable

    Details

    • 19 status:
      Verified
    • 12 status:
      Please Merge
    • Test Plan:
      Hide

      Use EXPLAIN and the query from the description to ensure the new index is used.

      Show
      Use EXPLAIN and the query from the description to ensure the new index is used.

      Description

      There is no index on ASN_SUBMISSION_SUBMITTER -> SUBMITTER. There is only a composite index that is of no help (MySQL: https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html)

      For a class with thousands of students, this is the query to pull each student submission:

      SELECT
      	this_.SUBMISSION_ID AS SUBMISSI1_10_2_,
      	this_.ASSIGNMENT_ID AS ASSIGNM20_10_2_,
      	this_.CREATED_DATE AS CREATED_2_10_2_,
      	this_.MODIFIED_DATE AS MODIFIED3_10_2_,
      	this_.RETURNED_DATE AS RETURNED4_10_2_,
      	this_.SUBMITTED_DATE AS SUBMITTE5_10_2_,
      	this_.FACTOR AS FACTOR6_10_2_,
      	this_.FEEDBACK_COMMENT AS FEEDBACK7_10_2_,
      	this_.FEEDBACK_TEXT AS FEEDBACK8_10_2_,
      	this_.GRADE AS GRADE9_10_2_,
      	this_.GRADE_RELEASED AS GRADE_R10_10_2_,
      	this_.GRADED AS GRADED11_10_2_,
      	this_.GRADED_BY AS GRADED_12_10_2_,
      	this_.GROUP_ID AS GROUP_I13_10_2_,
      	this_.HIDDEN_DUE_DATE AS HIDDEN_14_10_2_,
      	this_.HONOR_PLEDGE AS HONOR_P15_10_2_,
      	this_.RETURNED AS RETURNE16_10_2_,
      	this_.SUBMITTED AS SUBMITT17_10_2_,
      	this_.TEXT AS TEXT18_10_2_,
      	this_.USER_SUBMISSION AS USER_SU19_10_2_,
      	s1_.ID AS ID1_14_0_,
      	s1_.FEEDBACK AS FEEDBACK2_14_0_,
      	s1_.GRADE AS GRADE3_14_0_,
      	s1_.SUBMISSION_ID AS SUBMISSI6_14_0_,
      	s1_.SUBMITTEE AS SUBMITTE4_14_0_,
      	s1_.SUBMITTER AS SUBMITTE5_14_0_,
      	assignment4_.SUBMISSION_ID AS SUBMISSI1_10_1_,
      	assignment4_.ASSIGNMENT_ID AS ASSIGNM20_10_1_,
      	assignment4_.CREATED_DATE AS CREATED_2_10_1_,
      	assignment4_.MODIFIED_DATE AS MODIFIED3_10_1_,
      	assignment4_.RETURNED_DATE AS RETURNED4_10_1_,
      	assignment4_.SUBMITTED_DATE AS SUBMITTE5_10_1_,
      	assignment4_.FACTOR AS FACTOR6_10_1_,
      	assignment4_.FEEDBACK_COMMENT AS FEEDBACK7_10_1_,
      	assignment4_.FEEDBACK_TEXT AS FEEDBACK8_10_1_,
      	assignment4_.GRADE AS GRADE9_10_1_,
      	assignment4_.GRADE_RELEASED AS GRADE_R10_10_1_,
      	assignment4_.GRADED AS GRADED11_10_1_,
      	assignment4_.GRADED_BY AS GRADED_12_10_1_,
      	assignment4_.GROUP_ID AS GROUP_I13_10_1_,
      	assignment4_.HIDDEN_DUE_DATE AS HIDDEN_14_10_1_,
      	assignment4_.HONOR_PLEDGE AS HONOR_P15_10_1_,
      	assignment4_.RETURNED AS RETURNE16_10_1_,
      	assignment4_.SUBMITTED AS SUBMITT17_10_1_,
      	assignment4_.TEXT AS TEXT18_10_1_,
      	assignment4_.USER_SUBMISSION AS USER_SU19_10_1_ 
      FROM
      	ASN_SUBMISSION this_
      	INNER JOIN ASN_SUBMISSION_SUBMITTER s1_ ON this_.SUBMISSION_ID = s1_.SUBMISSION_ID
      	LEFT OUTER JOIN ASN_SUBMISSION assignment4_ ON s1_.SUBMISSION_ID = assignment4_.SUBMISSION_ID 
      WHERE
      	this_.ASSIGNMENT_ID =87620
      	AND s1_.SUBMITTER = 'x'
      

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                ern Earle R Nietzel
                Reporter:
                ottenhoff Sam Ottenhoff
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Git Source Code