Thank you Mathias once again
> I suggest you to test after optimize table ... and analyze table ...
> if you stay with the problem and you can give me a set of data, i'll try to
> help
> more.
Initially while posting... i tried to reduce one table to avoid
complexity from the actual query. I'm listing the tables below. I'm
also attaching some sample data.
-------------------------------------------------------------------------------------------------------------------------------------
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 `testId` (`testId`),
KEY `testStudent` (`id`,`testId`,`studentId`)
) 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;
CREATE TABLE `ums_user` (
`id` bigint(255) NOT NULL auto_increment,
`firstName` varchar(255) NOT NULL default '',
`lastName` varchar(255) NOT NULL default '',
`userName` varchar(50) NOT NULL default '',
`password` varchar(50) NOT NULL default '',
`email` varchar(100) NOT NULL default '',
`companyId` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `userName` (`userName`),
KEY `roleId` (`id`)
) TYPE=MyISAM ;
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;
-------------------------------------------------------------------------------------------------------------------------------------
Now the below query is not using the index "testStudent" in qb_test_result.
-------------------------------------------------------------------------------------------------------------------------------------
EXPLAIN
SELECT ums_user.firstName, qb_test.title testName, SUM(
qb_question.marks ) maxMarks, qb_test_result.percentage
FROM qb_test_result, qb_test_result_details, qb_test, qb_question,
ums_user
WHERE qb_test_result.id = qb_test_result_details.resultId
AND qb_test_result.testId = qb_test.id
AND qb_test_result.studentId = ums_user.id
AND qb_test.id = qb_question.testId
AND qb_question.id = qb_test_result_details.questionId
AND qb_test.companyId =1
AND qb_test.author = 2
GROUP BY qb_test_result.id
-------------------------------------------------------------------------------------------------------------------------------------
How ever when i removed qb_test_result.percentage...it does..
-------------------------------------------------------------------------------------------------------------------------------------
EXPLAIN
SELECT ums_user.firstName, qb_test.title testName, SUM(
qb_question.marks ) maxMarks
FROM qb_test_result, qb_test_result_details, qb_test, qb_question,
ums_user
WHERE qb_test_result.id = qb_test_result_details.resultId
AND qb_test_result.testId = qb_test.id
AND qb_test_result.studentId = ums_user.id
AND qb_test.id = qb_question.testId
AND qb_question.id = qb_test_result_details.questionId
AND qb_test.companyId =1
AND qb_test.author = 2
GROUP BY qb_test_result.id
-------------------------------------------------------------------------------------------------------------------------------------
Am i doing something wrong in the first query
Thank you once again
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
