I was trying to analyze a query that was taking almost 4 seconds to
execute. While trying to create additional indexes - found that the
query is not using any index from table qb_test_result . The type
returned is ALL for qb_test_result .
I have given the tables and query below. It would have been a help if
some one could throw some light on why this is behaving so.. I tried
to read the mysql manual and follow the
steps given there.
CREATE TABLE `qb_question` (
`id` int(11) NOT NULL auto_increment,
`question` text NOT NULL,
`url` varchar(255) NOT NULL default '',
`file` varchar(255) NOT NULL default '',
`marks` int(11) NOT NULL default '0',
`detailedAnswer` text NOT NULL,
`author` int(11) NOT NULL default '0',
`testId` smallint(4) NOT NULL default '0',
`loId` int(11) NOT NULL default '0',
`needWrittenAnswer` enum('Y','N') NOT NULL default 'Y',
`archive` enum('Y','N') NOT NULL default 'N',
PRIMARY KEY (`id`),
KEY `testId` (`testId`,`archive`)
) TYPE=MyISAM ;
CREATE TABLE `qb_test` (
`id` smallint(4) NOT NULL auto_increment,
`categoryId` int(11) NOT NULL default '0',
`title` varchar(80) NOT NULL default '',
`description` text NOT NULL,
`instructions` text NOT NULL,
`author` int(4) NOT NULL default '0',
`type` enum('PUBLIC','POST','PRE','REV') default NULL,
`duration` smallint(6) NOT NULL default '0',
`passrate` float NOT NULL default '0',
`showDetails` enum('Y','N') NOT NULL default 'Y',
`showRandom` enum('Y','N') NOT NULL default 'Y',
`showAssessment` enum('N','Y') NOT NULL default 'N',
`noOfQuestions` int(11) NOT NULL default '0',
`dateAvailable` datetime NOT NULL default '0000-00-00 00:00:00',
`companyId` int(11) NOT NULL default '0',
`archive` enum('Y','N') NOT NULL default 'N',
PRIMARY KEY (`id`),
KEY `title` (`title`,`author`,`type`),
KEY `categoryId` (`categoryId`),
KEY `companyAuthor` (`companyId`,`author`)
) TYPE=MyISAM ;
CREATE TABLE `qb_test_result` (
`id` int(11) NOT NULL auto_increment,
`testId` smallint(4) NOT NULL default '0',
`studentId` int(11) NOT NULL default '0',
`marks` smallint(4) NOT NULL default '0',
`startTime` int(20) default NULL,
`endTime` int(20) default NULL,
`percentage` float NOT NULL default '0',
`status` enum('FAIL','PASS','POST','UNCOMPLETE') NOT NULL default
'UNCOMPLETE',
PRIMARY KEY (`id`),
KEY `testStudent` (`testId`)
) TYPE=MyISAM ;
CREATE TABLE `qb_test_result_details` (
`sequenceId` int(20) NOT NULL default '0',
`resultId` int(20) NOT NULL default '0',
`questionId` int(20) NOT NULL default '0',
`viewStatus` enum('NV','V','A') NOT NULL default 'NV',
`bookMark` enum('Y','N') NOT NULL default 'N',
`correct` enum('Y','N') NOT NULL default 'N',
`postMarks` int(11) NOT NULL default '0',
KEY `resultId` (`resultId`)
) TYPE=MyISAM ;
EXPLAIN SELECT qb_test_result.id resultId, qb_question.testId AS
testId, qb_test.title testName, qb_question.marks, qb_test.passrate,
qb_test_result.marks testMark, qb_test_result.percentage
testPercentage, qb_test_result.startTime, qb_test_result.endTime,
qb_test_result.status
FROM qb_test_result, qb_test_result_details, qb_test, qb_question
WHERE qb_test_result.id = qb_test_result_details.resultId
AND qb_test_result.testId = qb_test.id
AND qb_test.companyId =1
AND qb_test.author = '2'
AND qb_test_result_details.questionId = qb_question.id
+------------------------+--------+-----------------------+----------+---------+-----------------------------------+------+-------------+
| table | type | possible_keys | key |
key_len | ref | rows | Extra |
+------------------------+--------+-----------------------+----------+---------+-----------------------------------+------+-------------+
| qb_test_result | ALL | PRIMARY,testStudent | NULL |
NULL | NULL | 2494 | |
| qb_test_result_details | ref | resultId | resultId |
4 | qb_test_result.id | 45 | |
| qb_test | eq_ref | PRIMARY,companyAuthor | PRIMARY |
2 | qb_test_result.testId | 1 | Using where |
| qb_question | eq_ref | PRIMARY | PRIMARY |
4 | qb_test_result_details.questionId | 1 | |
+------------------------+--------+-----------------------+----------+---------+-----------------------------------+------+-------------+
Thank you
Sajith A
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
