[Building Sakai] Question pool slowness

[Karen Tsao]

Hi George,

If you are using MySql, you don't need to add the four indexes in
sakai_samigo_post_schema_update.sql. They are duplicate indexes.

By the way, the index VT_SAM_ITEMGRADING_AG_PUBITEM is not related to
editing Question Pool. As Will mentioned, they use this to improve delivery
and grading flow. You can gather your recent statistics and see if you need
to add this index.

Thanks,
Karen

On Wed, Sep 9, 2009 at 9:32 AM, George Pipkin <gpp8p at virginia.edu> wrote:

> Hi -
>
>  Thanks Karen and Will for your responses.  I took a look at
> our tables current setup, and it looks like we not only got the
> hibernate-created indexes, but the ones mentioned in
> the sakai_samigo_post_schema_update.sql  script.  Could this be
> causing us difficulties ?
>
>                              - George
>
> mysql>  show create table SAM_PUBLISHEDITEMTEXT_T;
> | SAM_PUBLISHEDITEMTEXT_T | CREATE TABLE `SAM_PUBLISHEDITEMTEXT_T` (
>  `ITEMTEXTID` bigint(20) NOT NULL auto_increment,
>  `ITEMID` bigint(20) NOT NULL,
>  `SEQUENCE` int(11) NOT NULL,
>  `TEXT` varchar(4000) default NULL,
>  PRIMARY KEY  (`ITEMTEXTID`),
>  KEY `FK9C790A638152036E` (`ITEMID`),
>  KEY `SAM_PUBITEMTEXT_ITEM_I` (`ITEMID`),
>  KEY `FK9C790A6331446627` (`ITEMID`),
>  CONSTRAINT `FK9C790A6331446627` FOREIGN KEY (`ITEMID`) REFERENCES
> `SAM_PUBLISHEDITEM_T` (`ITEMID`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
>
> mysql> show create table SAM_PUBLISHEDANSWER_T;
> | SAM_PUBLISHEDANSWER_T | CREATE TABLE `SAM_PUBLISHEDANSWER_T` (
>  `ANSWERID` bigint(20) NOT NULL auto_increment,
>  `ITEMTEXTID` bigint(20) NOT NULL,
>  `itemId` bigint(20) NOT NULL,
>  `TEXT` varchar(4000) default NULL,
>  `SEQUENCE` int(11) NOT NULL,
>  `LABEL` varchar(20) default NULL,
>  `ISCORRECT` varchar(1) default NULL,
>  `GRADE` varchar(80) default NULL,
>  `SCORE` float default NULL,
>  PRIMARY KEY  (`ANSWERID`),
>  KEY `FKB41EA361B9BF0B8E` (`itemId`),
>  KEY `FKB41EA361CBA347DB` (`ITEMTEXTID`),
>  KEY `SAM_PUBANSWER_ITEMTEXT_I` (`ITEMTEXTID`),
>  KEY `SAM_PUBANSWER_ITEM_I` (`itemId`),
>  KEY `FKB41EA3618152036E` (`itemId`),
>  KEY `FKB41EA36131446627` (`itemId`),
>  KEY `FKB41EA36126460817` (`ITEMTEXTID`),
>  CONSTRAINT `FKB41EA36126460817` FOREIGN KEY (`ITEMTEXTID`) REFERENCES
> `SAM_PUBLISHEDITEMTEXT_T` (`ITEMTEXTID`),
>  CONSTRAINT `FKB41EA36131446627` FOREIGN KEY (`itemId`) REFERENCES
> `SAM_PUBLISHEDITEM_T` (`ITEMID`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
>
> mysql> show create table SAM_ITEMGRADING_T;
> | SAM_ITEMGRADING_T | CREATE TABLE `SAM_ITEMGRADING_T` (
>  `ITEMGRADINGID` bigint(20) NOT NULL auto_increment,
>  `ASSESSMENTGRADINGID` bigint(20) NOT NULL,
>  `PUBLISHEDITEMID` int(11) NOT NULL,
>  `PUBLISHEDITEMTEXTID` int(11) NOT NULL,
>  `AGENTID` varchar(255) NOT NULL,
>  `SUBMITTEDDATE` datetime default NULL,
>  `PUBLISHEDANSWERID` int(11) default NULL,
>  `RATIONALE` varchar(4000) default NULL,
>  `ANSWERTEXT` 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` int(11) default NULL,
>  `ATTEMPTSREMAINING` int(11) default NULL,
>  `LASTDURATION` varchar(36) default NULL,
>  PRIMARY KEY  (`ITEMGRADINGID`),
>  KEY `FKB68E6756C42AA2BC` (`PUBLISHEDITEMID`),
>  KEY `FKB68E6756E5D3D24D` (`ASSESSMENTGRADINGID`),
>  KEY `FKB68E6756A75F9029` (`PUBLISHEDITEMTEXTID`),
>  KEY `FKB68E6756D4927` (`PUBLISHEDANSWERID`),
>  KEY `SAM_ASSESSMENTGRADING_I` (`ASSESSMENTGRADINGID`),
>  KEY `SAM_ITEMGRADING_PUBANS_I` (`PUBLISHEDANSWERID`),
>  KEY `SAM_ITEMGRADING_ITEM_I` (`PUBLISHEDITEMID`),
>  KEY `SAM_ITEMGRADING_ITEMTEXT_I` (`PUBLISHEDITEMTEXTID`),
>  KEY `FKB68E675667B430D5` (`ASSESSMENTGRADINGID`),
>  CONSTRAINT `FKB68E675667B430D5` FOREIGN KEY (`ASSESSMENTGRADINGID`)
> REFERENCES `SAM_ASSESSMENTGRADING_T` (`ASSESSMENTGRADINGID`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
>
> mysql> show create table SAM_PUBLISHEDANSWERFEEDBACK_T;
> | SAM_PUBLISHEDANSWERFEEDBACK_T | CREATE TABLE
> `SAM_PUBLISHEDANSWERFEEDBACK_T` (
>  `ANSWERFEEDBACKID` bigint(20) NOT NULL auto_increment,
>  `ANSWERID` bigint(20) NOT NULL,
>  `TYPEID` varchar(255) default NULL,
>  `TEXT` varchar(4000) default NULL,
>  PRIMARY KEY  (`ANSWERFEEDBACKID`),
>  KEY `FK6CB765A610DF4559` (`ANSWERID`),
>  KEY `SAM_PUBANSWERFB_ANSWER_I` (`ANSWERID`),
>  KEY `FK6CB765A624D77573` (`ANSWERID`),
>  CONSTRAINT `FK6CB765A624D77573` FOREIGN KEY (`ANSWERID`) REFERENCES
> `SAM_PUBLISHEDANSWER_T` (`ANSWERID`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
> mysql> show create table SAM_ASSESSMENTGRADING_T;
> | SAM_ASSESSMENTGRADING_T | 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` varchar(1) NOT NULL,
>  `FORGRADE` int(11) 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,
>  PRIMARY KEY  (`ASSESSMENTGRADINGID`),
>  KEY `FKDAED4C879E4AF02B` (`PUBLISHEDASSESSMENTID`),
>  KEY `SAM_PUBLISHEDASSESSMENT_I` (`PUBLISHEDASSESSMENTID`),
>  KEY `SAM_ASSGRAD_AID_PUBASSEID_T` (`AGENTID`,`PUBLISHEDASSESSMENTID`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
>
> mysql>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20090909/a265bd43/attachment.html