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

[Kusnetz, Jeremy]

We just upgraded to Sakai 2.9.x (close to 2.9.3 RC01).

I'm seeing some pretty long queries coming from Samigo, taking upwards of 30 seconds or more.  We did not see this in 2.8.

I'm not sure if we can fix it with an index, or the sql needs to be refactored.

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=267345 and assessment2_.FORGRADE=1 and publishedi1_.SECTIONID=853460 and itemgradin3_.ASSESSMENTGRADINGID=assessment2_.ASSESSMENTGRADINGID and publishedi1_.ITEMID=itemgradin3_.PUBLISHEDITEMID);

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: publishedi0_
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8351034
        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> show create table SAM_PUBLISHEDITEM_T\G
*************************** 1. row ***************************
       Table: SAM_PUBLISHEDITEM_T
Create Table: CREATE TABLE `SAM_PUBLISHEDITEM_T` (
  `ITEMID` bigint(20) NOT NULL AUTO_INCREMENT,
  `SECTIONID` bigint(20) NOT NULL,
  `ITEMIDSTRING` varchar(255) DEFAULT NULL,
  `SEQUENCE` int(11) DEFAULT NULL,
  `DURATION` int(11) DEFAULT NULL,
  `TRIESALLOWED` int(11) DEFAULT NULL,
  `INSTRUCTION` varchar(4000) DEFAULT NULL,
  `DESCRIPTION` varchar(4000) DEFAULT NULL,
  `TYPEID` bigint(20) NOT NULL,
  `GRADE` varchar(80) DEFAULT NULL,
  `SCORE` float DEFAULT NULL,
  `DISCOUNT` float DEFAULT NULL,
  `HINT` varchar(4000) DEFAULT NULL,
  `HASRATIONALE` bit(1) DEFAULT NULL,
  `STATUS` int(11) NOT NULL,
  `CREATEDBY` varchar(255) NOT NULL,
  `CREATEDDATE` datetime NOT NULL,
  `LASTMODIFIEDBY` varchar(255) NOT NULL,
  `LASTMODIFIEDDATE` datetime NOT NULL,
  `PARTIAL_CREDIT_FLAG` bit(1) DEFAULT NULL,
  PRIMARY KEY (`ITEMID`),
  KEY `FK53ABDCF6895D4813` (`SECTIONID`),
  CONSTRAINT `FK53ABDCF6895D4813` FOREIGN KEY (`SECTIONID`) REFERENCES `SAM_PUBLISHEDSECTION_T` (`SECTIONID`)
) ENGINE=InnoDB AUTO_INCREMENT=9829483 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table SAM_ASSESSMENTGRADING_T\G
*************************** 1. row ***************************
       Table: SAM_ASSESSMENTGRADING_T
Create Table: CREATE TABLE `SAM_ASSESSMENTGRADING_T` (
  `ASSESSMENTGRADINGID` bigint(20) NOT NULL AUTO_INCREMENT,
  `PUBLISHEDASSESSMENTID` bigint(20) NOT NULL,
  `AGENTID` varchar(255) NOT NULL,
  `SUBMITTEDDATE` datetime DEFAULT NULL,
  `ISLATE` bit(1) NOT NULL,
  `FORGRADE` bit(1) NOT NULL,
  `TOTALAUTOSCORE` float DEFAULT NULL,
  `TOTALOVERRIDESCORE` float DEFAULT NULL,
  `FINALSCORE` float DEFAULT NULL,
  `COMMENTS` varchar(4000) DEFAULT NULL,
  `GRADEDBY` varchar(255) DEFAULT NULL,
  `GRADEDDATE` datetime DEFAULT NULL,
  `STATUS` int(11) NOT NULL,
  `ATTEMPTDATE` datetime DEFAULT NULL,
  `TIMEELAPSED` int(11) DEFAULT NULL,
  `ISAUTOSUBMITTED` bit(1) DEFAULT NULL,
  `LASTVISITEDPART` int(11) DEFAULT NULL,
  `LASTVISITEDQUESTION` int(11) DEFAULT NULL,
  PRIMARY KEY (`ASSESSMENTGRADINGID`),
  KEY `SAM_PUBLISHEDASSESSMENT_I` (`PUBLISHEDASSESSMENTID`),
  KEY `SAM_ASSGRAD_AID_PUBASSEID_T` (`AGENTID`,`PUBLISHEDASSESSMENTID`)
) ENGINE=InnoDB AUTO_INCREMENT=3243761 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table SAM_ITEMGRADING_T\G
*************************** 1. row ***************************
       Table: SAM_ITEMGRADING_T
Create Table: CREATE TABLE `SAM_ITEMGRADING_T` (
  `ITEMGRADINGID` bigint(20) NOT NULL AUTO_INCREMENT,
  `ASSESSMENTGRADINGID` bigint(20) NOT NULL,
  `PUBLISHEDITEMID` bigint(20) NOT NULL,
  `PUBLISHEDITEMTEXTID` bigint(20) NOT NULL,
  `AGENTID` varchar(255) NOT NULL,
  `SUBMITTEDDATE` datetime DEFAULT NULL,
  `PUBLISHEDANSWERID` bigint(20) DEFAULT NULL,
  `RATIONALE` varchar(4000) DEFAULT NULL,
  `AUTOSCORE` float DEFAULT NULL,
  `OVERRIDESCORE` float DEFAULT NULL,
  `COMMENTS` varchar(4000) DEFAULT NULL,
  `GRADEDBY` varchar(255) DEFAULT NULL,
  `GRADEDDATE` datetime DEFAULT NULL,
  `REVIEW` bit(1) DEFAULT NULL,
  `ATTEMPTSREMAINING` int(11) DEFAULT NULL,
  `LASTDURATION` varchar(36) DEFAULT NULL,
  `ANSWERTEXT` text,
  `ISCORRECT` bit(1) DEFAULT NULL,
  PRIMARY KEY (`ITEMGRADINGID`),
  KEY `FKB68E675667B430D5` (`ASSESSMENTGRADINGID`),
  KEY `SAM_ITEMGRADING_ITEM_I` (`PUBLISHEDITEMID`),
  KEY `SAM_ITEMGRADING_ITEMTEXT_I` (`PUBLISHEDITEMTEXTID`),
  KEY `SAM_ITEMGRADING_PUBANS_I` (`PUBLISHEDANSWERID`),
  CONSTRAINT `FKB68E675667B430D5` FOREIGN KEY (`ASSESSMENTGRADINGID`) REFERENCES `SAM_ASSESSMENTGRADING_T` (`ASSESSMENTGRADINGID`)
) ENGINE=InnoDB AUTO_INCREMENT=72666685 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
This message is private and confidential. If you have received it in error, please notify the sender and remove it from your system.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20130730/35b60b01/attachment.html