Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

This query is taking a long time to execute. We are expecting results in 0 sec, if possible.

Please help me to do any other following:

  • Rewrite the query
  • Suggest any indexes
  • Any other optimization that may be needed.

Also, please help isolate root cause.

Here is the Query And Explain Plan:

mysql> explain SELECT * FROM (`xgen_studentMLs`)
    LEFT JOIN `xgen_quizzes`
         ON xgen_studentMLs.quizid = xgen_quizzes.idnum
          `student` = 27126
      AND xgen_studentMLs.topic = 1829
      AND xgen_studentMLs.metatype = 'topic'
      AND (xgen_studentMLs.department='Nursing' OR xgen_studentMLs.department='Biology')
      AND ( IS NULL OR
    ORDER BY xgen_studentMLs.timestamp DESC LIMIT 100;

| id | select_type | table           | type   | possible_keys | key     | key_len | ref                          | rows | Extra                       |
|  1 | SIMPLE      | xgen_studentMLs | range  | student,mult  | mult    | 265     | NULL                         |   18 | Using where; Using filesort |
|  1 | SIMPLE      | xgen_quizzes    | eq_ref | PRIMARY       | PRIMARY | 4       | prepu.xgen_studentMLs.quizid |    1 | Using where                 |

Here are table structures:

mysql> show create table xgen_quizzes\G
*************************** 1. row ***************************
       Table: xgen_quizzes
Create Table: CREATE TABLE `xgen_quizzes` (
  `idnum` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL DEFAULT '0',
  `timestarted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `timefinished` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `questionlist` mediumtext NOT NULL,
  `topics` mediumtext NOT NULL,
  `totalnum` int(11) NOT NULL DEFAULT '0',
  `completednum` int(11) NOT NULL DEFAULT '0',
  `assignment` int(11) NOT NULL DEFAULT '0',
  `department` varchar(255) NOT NULL DEFAULT '',
  `book` int(11) NOT NULL DEFAULT '0',
  `cqs` mediumtext NOT NULL,
  `metatype` varchar(25) DEFAULT 'topic',
  PRIMARY KEY (`idnum`),
  KEY `userid` (`userid`),
  KEY `assignment` (`assignment`)
1 row in set (0.00 sec)

mysql> show create table xgen_studentMLs\G
*************************** 1. row ***************************
       Table: xgen_studentMLs
Create Table: CREATE TABLE `xgen_studentMLs` (
  `student` int(11) NOT NULL,
  `topic` int(11) NOT NULL,
  `ML` float NOT NULL,
  `MI` int(3) NOT NULL DEFAULT '0',
  `department` varchar(255) NOT NULL,
  `metatype` varchar(25) DEFAULT 'topic',
  `quizid` int(11) NOT NULL DEFAULT '0',
  KEY `student` (`student`),
  KEY `mult` (`topic`,`department`,`student`,`timestamp`,`ML`)
1 row in set (0.00 sec)
share|improve this question
A result returned in 0 (zero) seconds is impossible. That means that the database didn't do anything. – a_horse_with_no_name Mar 20 '13 at 9:40
This is going to need some complex indexes but why haven't you at least added an index on studentMLs (quizid) that you use for joining? And shouldn't there be a FOREIGN KEY constraint on that column, to reference quizzes (idnum)? – ypercubeᵀᴹ May 21 '13 at 14:51
And why the 2nd table doesn't have a primary key? – ypercubeᵀᴹ May 21 '13 at 14:51

Here is your query refactored

(SELECT * FROM `xgen_studentMLs`
WHERE student = 27126 AND topic = 1829 AND metatype = 'topic'
AND department IN ('Nursing','Biology')
ORDER BY timestamp DESC LIMIT 100) A
LEFT JOIN xgen_quizzes Q ON A.quizid = Q.idnum

The subquery is meant to retrieve only 100 rows before doing the LEFT JOIN. You will need an index to assist the subquery

ALTER TABLE xgen_studentMLs ADD INDEX new_ndx (student,metatype,topic,department);

Give it a Try !!!

share|improve this answer
Is IFNULL(,0) = 0 more efficient than ( IS NULL OR Oh sorry, your idea is to limit first to 100 rows, so this won't matter much. – ypercubeᵀᴹ May 21 '13 at 14:38
@ypercube If is NULL, then should not be evaluted. Perhaps IFNULL performs the same way. The docs say that it uses IF THEN ELSE construct (… ). I would have to look at the source to verify that. I suppose calling a function requires a tiny bit of overhead when evaluating the query per row versus a direct IF THEN ELSE might make it nominally slower. One would have to benchmark that. – RolandoMySQLDBA May 21 '13 at 14:42

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.