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 (xgen_quizzes.book IS NULL OR xgen_quizzes.book=0)
    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)
  • 1
    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
WHERE IFNULL(Q.book,0) = 0;

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

  • Is IFNULL(Q.book,0) = 0 more efficient than (Q.book IS NULL OR Q.book=0)? 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 Q.book is NULL, then Q.book=0 should not be evaluted. Perhaps IFNULL performs the same way. The docs say that it uses IF THEN ELSE construct ( dev.mysql.com/doc/refman/5.5/en/… ). 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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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