[samigo-team] [Building Sakai] FW: I am seeing on slow query related to samigo

[Kusnetz, Jeremy]

mysql> explain select publishedi0_.ITEMID as ITEMID212_, publishedi0_.SECTIONID as SECTIONID212_, publishedi0_.ITEMIDSTRING as ITEMIDST3_212_, publishedi0_.SEQUENCE as SEQUENCE212_, publishedi0_.DURATION as DURATION212_, publishedi0_.TRIESALLOWED as TRIESALL6_212_, publishedi0_.INSTRUCTION as INSTRUCT7_212_, publishedi0_.DESCRIPTION as DESCRIPT8_212_, publishedi0_.TYPEID as TYPEID212_, publishedi0_.GRADE as GRADE212_, publishedi0_.SCORE as SCORE212_, publishedi0_.DISCOUNT as DISCOUNT212_, publishedi0_.HINT as HINT212_, publishedi0_.HASRATIONALE as HASRATI14_212_, publishedi0_.PARTIAL_CREDIT_FLAG as PARTIAL15_212_, publishedi0_.STATUS as STATUS212_, publishedi0_.CREATEDBY as CREATEDBY212_, publishedi0_.CREATEDDATE as CREATED18_212_, publishedi0_.LASTMODIFIEDBY as LASTMOD19_212_, publishedi0_.LASTMODIFIEDDATE as LASTMOD20_212_ from SAM_PUBLISHEDITEM_T publishedi0_ where publishedi0_.ITEMID in (select publishedi1_.ITEMID from SAM_PUBLISHEDITEM_T publishedi1_, SAM_ASSESSMENTGRADING_T assessment2_, SAM_ITEMGRADING_T itemgradin3_ where assessment2_.PUBLISHEDASSESSMENTID=249810 and assessment2_.FORGRADE=1 and publishedi1_.SECTIONID=779914 and itemgradin3_.ASSESSMENTGRADINGID=assessment2_.ASSESSMENTGRADINGID and publishedi1_.ITEMID=itemgradin3_.PUBLISHEDITEMID)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: publishedi0_
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8353561
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: publishedi1_
         type: eq_ref
possible_keys: PRIMARY,FK53ABDCF6895D4813
          key: PRIMARY
      key_len: 8
          ref: func
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: itemgradin3_
         type: ref
possible_keys: FKB68E675667B430D5,SAM_ITEMGRADING_ITEM_I
          key: SAM_ITEMGRADING_ITEM_I
      key_len: 8
          ref: func
         rows: 5
        Extra: Using where
*************************** 4. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: assessment2_
         type: eq_ref
possible_keys: PRIMARY,SAM_PUBLISHEDASSESSMENT_I
          key: PRIMARY
      key_len: 8
          ref: sakai_100.itemgradin3_.ASSESSMENTGRADINGID
         rows: 1
        Extra: Using where
4 rows in set (0.00 sec) mysql> explain select publishedi0_.ITEMID as ITEMID212_, publishedi0_.SECTIONID as SECTIONID212_, publishedi0_.ITEMIDSTRING as ITEMIDST3_212_, publishedi0_.SEQUENCE as SEQUENCE212_, publishedi0_.DURATION as DURATION212_, publishedi0_.TRIESALLOWED as TRIESALL6_212_, publishedi0_.INSTRUCTION as INSTRUCT7_212_, publishedi0_.DESCRIPTION as DESCRIPT8_212_, publishedi0_.TYPEID as TYPEID212_, publishedi0_.GRADE as GRADE212_, publishedi0_.SCORE as SCORE212_, publishedi0_.DISCOUNT as DISCOUNT212_, publishedi0_.HINT as HINT212_, publishedi0_.HASRATIONALE as HASRATI14_212_, publishedi0_.PARTIAL_CREDIT_FLAG as PARTIAL15_212_, publishedi0_.STATUS as STATUS212_, publishedi0_.CREATEDBY as CREATEDBY212_, publishedi0_.CREATEDDATE as CREATED18_212_, publishedi0_.LASTMODIFIEDBY as LASTMOD19_212_, publishedi0_.LASTMODIFIEDDATE as LASTMOD20_212_ from SAM_PUBLISHEDITEM_T publishedi0_ where publishedi0_.ITEMID in (select publishedi1_.ITEMID from SAM_PUBLISHEDITEM_T publishedi1_, SAM_ASSESSMENTGRADING_T assessment2_, SAM_ITEMGRADING_T itemgradin3_ where assessment2_.PUBLISHEDASSESSMENTID=249810 and assessment2_.FORGRADE=1 and publishedi1_.SECTIONID=779914 and itemgradin3_.ASSESSMENTGRADINGID=assessment2_.ASSESSMENTGRADINGID and publishedi1_.ITEMID=itemgradin3_.PUBLISHEDITEMID)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: publishedi0_
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8353561
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: publishedi1_
         type: eq_ref
possible_keys: PRIMARY,FK53ABDCF6895D4813
          key: PRIMARY
      key_len: 8
          ref: func
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: itemgradin3_
         type: ref
possible_keys: FKB68E675667B430D5,SAM_ITEMGRADING_ITEM_I
          key: SAM_ITEMGRADING_ITEM_I
      key_len: 8
          ref: func
         rows: 5
        Extra: Using where
*************************** 4. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: assessment2_
         type: eq_ref
possible_keys: PRIMARY,SAM_PUBLISHEDASSESSMENT_I
          key: PRIMARY
      key_len: 8
          ref: sakai_100.itemgradin3_.ASSESSMENTGRADINGID
         rows: 1
        Extra: Using where
4 rows in set (0.00 sec)


Here is the sub query:


mysql> explain select publishedi1_.ITEMID from SAM_PUBLISHEDITEM_T publishedi1_, SAM_ASSESSMENTGRADING_T assessment2_, SAM_ITEMGRADING_T itemgradin3_ where assessment2_.PUBLISHEDASSESSMENTID=249810 and assessment2_.FORGRADE=1 and publishedi1_.SECTIONID=779914 and itemgradin3_.ASSESSMENTGRADINGID=assessment2_.ASSESSMENTGRADINGID and publishedi1_.ITEMID=itemgradin3_.PUBLISHEDITEMID\G                *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: publishedi1_
         type: ref
possible_keys: PRIMARY,FK53ABDCF6895D4813
          key: FK53ABDCF6895D4813
      key_len: 8
          ref: const
         rows: 3
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: itemgradin3_
         type: ref
possible_keys: FKB68E675667B430D5,SAM_ITEMGRADING_ITEM_I
          key: SAM_ITEMGRADING_ITEM_I
      key_len: 8
          ref: sakai_100.publishedi1_.ITEMID
         rows: 5
        Extra:
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: assessment2_
         type: eq_ref
possible_keys: PRIMARY,SAM_PUBLISHEDASSESSMENT_I
          key: PRIMARY
      key_len: 8
          ref: sakai_100.itemgradin3_.ASSESSMENTGRADINGID
         rows: 1
        Extra: Using where
3 rows in set (0.00 sec)


-----Original Message-----
From: Sam Ottenhoff [mailto:ottenhoff at longsight.com] 
Sent: Tuesday, July 30, 2013 1:17 PM
To: Kusnetz, Jeremy
Cc: azeckoski at unicon.net; sakai-dev at collab.sakaiproject.org; Samigo Team
Subject: Re: [Building Sakai] FW: I am seeing on slow query related to samigo

Can you send over your MySQL EXPLAIN result for the query (a table showing all joins and rows searched)?
This message is private and confidential. If you have received it in error, please notify the sender and remove it from your system.