0

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
    WHERE
          `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`)
) ENGINE=InnoDB AUTO_INCREMENT=13547573 DEFAULT CHARSET=latin1
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',
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `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`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
3
  • 1
    A result returned in 0 (zero) seconds is impossible. That means that the database didn't do anything.
    – user1822
    Commented Mar 20, 2013 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)? Commented May 21, 2013 at 14:51
  • And why the 2nd table doesn't have a primary key? Commented May 21, 2013 at 14:51

1 Answer 1

2

Here is your query refactored

SELECT * FROM
(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 !!!

2
  • 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. Commented May 21, 2013 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. Commented May 21, 2013 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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