from:"Sajith A"

Re: Indexing not working

2005-06-24 Thread Sajith A

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 '-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

Re: Indexing not working

2005-06-24 Thread Sajith A

Thank you Clark for your time
 Try using straight joins to force MySQL to join from qb_test_result first.
 The order chosen by the optimizer has no use for keys from this table.
I tried to force indexes.. but it didn't help
Thank you

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Indexing not working

2005-06-23 Thread Sajith A

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 '-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]

Re: Indexing not working

2005-06-23 Thread Sajith A

Thank you Mathias for your time...

 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.
Yes i also tried that .. but it was not getting used.. it listed as
the possible_keys PRIMARY and testStudent but the key value was
NULL.. I tried to index all combination of fields in qb_test_result..
but nothing was getting use.. Am i doing  something wrong in the
query?

Thank you
Sajith A

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Indexing not working

Re: Indexing not working

Indexing not working

Re: Indexing not working

4 matches

Site Navigation

Mail list logo

Footer information