Looking for performance improvements for this query

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_quizzesG
*************************** 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_studentMLsG
*************************** 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)

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