Take the 2-minute tour ×
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. It's 100% free, no registration required.

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)
share|improve this question
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

1 Answer 1

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

share|improve this answer
    
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

 
discard

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.