[samigo-team] FW: I am seeing on slow query related to samigo
Aaron Zeckoski
azeckoski at unicon.net
Tue Jul 30 07:05:06 PDT 2013
Copying in the samigo team list.
-AZ
On Tue, Jul 30, 2013 at 9:44 AM, Kusnetz, Jeremy <JKusnetz at apus.edu> wrote:
> 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.
--
Aaron Zeckoski - Software Architect - http://tinyurl.com/azprofile
More information about the samigo-team
mailing list