[Building Sakai] Question pool slowness
Lydia Li
lydial at stanford.edu
Wed Sep 9 13:17:38 PDT 2009
Hi,
I'm not a DBA but having unnecessary indexes would cost overhead
during updates/inserts/deletes and the additional resources needed to
store and manage them. It's always a good idea to eliminate duplicate
indexes.
I found an article (a bit old though) that might be useful for
mysql users:
http://www.xaprb.com/blog/2006/08/28/how-to-find-duplicate-and-redundant-indexes-in-mysql/
And someone even filed a bug for mysql:
http://bugs.mysql.com/bug.php?id=37520
thanks,
Lydia
George Pipkin wrote:
> Do you think that having them there has any impact ?
>
> - George
>
>
> Karen Tsao wrote:
>
>> 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
>> <mailto: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>
>>
>>
>>
>
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>
> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"
>
More information about the sakai-dev
mailing list