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 morning I started receiving load warnings on one of the MySQL servers I manage. SHOW FULL PROCESSLIST revealed that the culprit was the following (poorly written) query:

SELECT * FROM movies, showtimes
WHERE site='5' AND
    (showtimes.movie = movies.id OR always='true') AND
    ((showdate>='2012-4-11' AND showdate<'2012-04-18') OR always='true')\
GROUP BY movies.id ORDER BY listorder

I'm not sure who wrote this. Looks like it's on a database I designed 5+ years ago, which unknown people have been tinkering with quite a bit since I last touched it. The tables in question are:

CREATE TABLE `movies` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(128) NOT NULL default '',
  `rating` varchar(5) NOT NULL default '',
  `description` varchar(255) NOT NULL default '',
  `special` varchar(255) NOT NULL default '',
  `url` varchar(64) NOT NULL default '',
  `poster` varchar(255) NOT NULL default '',
  `runtime` varchar(20) NOT NULL default '',
  `listorder` tinyint(4) NOT NULL default '0',
  `comsoon` enum('false','true') NOT NULL default 'false',
  `always` enum('false','true') NOT NULL default 'false',
  `site` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `name` (`name`),
  KEY `rating` (`rating`),
  KEY `site` (`site`),
  KEY `comsoon` (`comsoon`),
  KEY `always` (`always`),
  KEY `listorder` (`listorder`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE `showtimes` (
  `movie` int(11) NOT NULL default '0',
  `showdate` date NOT NULL default '0000-00-00',
  `showtime` time NOT NULL default '00:00:00',
  PRIMARY KEY  (`movie`,`showdate`,`showtime`),
  KEY `showdate` (`showdate`),
  KEY `showtime` (`showtime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

When I first was alerted to this situation, movies.always did not even have an index, nor did movies.listorder. I was sure that was the problem, however, adding an index on those two columns did not change the EXPLAIN:

mysql> EXPLAIN SELECT * FROM movies, showtimes WHERE site='5' AND (showtimes.movie = movies.id OR always='true') AND ((showdate>='2012-4-11' AND showdate<'2012-04-18') OR always='true')  GROUP BY movies.id ORDER BY listorder;
+----+-------------+-----------+-------+---------------------+---------+---------+------+-------+----------------------------------------------+
| id | select_type | table     | type  | possible_keys       | key     | key_len | ref  | rows  | Extra                                        |
+----+-------------+-----------+-------+---------------------+---------+---------+------+-------+----------------------------------------------+
|  1 | SIMPLE      | showtimes | index | PRIMARY,showdate    | PRIMARY | 10      | NULL | 93411 | Using index; Using temporary; Using filesort | 
|  1 | SIMPLE      | movies    | ALL   | PRIMARY,site,always | NULL    | NULL    | NULL |    25 | Using where                                  | 
+----+-------------+-----------+-------+---------------------+---------+---------+------+-------+----------------------------------------------+
2 rows in set (0.00 sec)

I tried rewriting the query as follows:

SELECT * FROM movies, showtimes
WHERE (site='5' AND always='true') OR 
  (site='5' AND showtimes.movie = movies.id AND showdate>='2012-4-11' AND showdate<'2012-04-18')
GROUP BY movies.id ORDER BY listorder

However the EXPLAIN was no different. WHy is this query not using an index on the movies table? How can I explain to the developers that they should rewrite this query to make it not bring the server to it's knees?

share|improve this question
2  
The always index is not going to be very helpful, it's not very selective. You should try adding a compound index: (always, site, id) or (site, always, id) –  ypercube Apr 11 '12 at 14:57
    
Plus, the GROUP BY movie.id seems wrong to be used with SELECT showtimes.* Which showtime should be shown from the many (for a specific movie)? –  ypercube Apr 11 '12 at 14:59
    
I have no idea what they're trying to do here. I guess they just want a list of all movies showing today? This is very bad code, LOL –  Josh Apr 11 '12 at 15:01
    
OK, but are you using the columns from showtime table? Or rewriting with SELECT movies.* ... is an option? Because then, rewriting with EXISTS will probably be an option, too. –  ypercube Apr 11 '12 at 15:14

2 Answers 2

up vote 2 down vote accepted

It might be possible to rewrite it into a UNION

SELECT * FROM (
 (
  SELECT movies.* FROM movies
   WHERE site='5' AND always='true'
 ) UNION
 (
  SELECT movies.* FROM movies
  INNER JOIN showtimes ON showtimes.movie=movies.id
  WHERE site='5' AND showdate BETWEEN '2012-4-11' AND '2012-04-18'
 )
) AS movie_shows
GROUP BY id
ORDER BY listorder

I haven't tested the above, but am curious the results of the EXPLAIN

Your always attribute is failing in two ways:

  1. It is very low selectivity as @ypercube points out, since there are only two possible values. MySQL will discard using these type of indexes

  2. The query has OR. Meaning it would have to do a table scan anyway to get match both conditions.

share|improve this answer
    
#1248 - Every derived table must have its own alias getting there! –  Josh Apr 11 '12 at 15:37
    
@Josh Finally decided to create the tables and test the query syntax out myself! It at least compiles, not sure on the viability of it without data. –  Derek Downey Apr 11 '12 at 15:39
    
This works great, thanks! –  Josh Apr 11 '12 at 16:39

You can also try this (avoiding the group by):

SELECT movies.* 
FROM movies 
WHERE site = 5
  AND always = 'true'
   OR site = 5
  AND always = 'false'
  AND EXISTS
      ( SELECT *
        FROM showtimes 
        WHERE movies.id = showtimes.movie
          AND showdate >= '2012-04-11' 
          AND showdate < '2012-04-18'
      ) 
ORDER BY listorder
share|improve this answer

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.