Hi,
have you created an index on (id, testId) ?
i can't see the other indexes ? The only ones are the PK and testStudent, so the
PK is used.
Mathias
Selon Sajith A <[EMAIL PROTECTED]>:
> 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]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
