Why isn't the following query using the `always` index?

Question

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?

Answer 1

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.

Answer 2

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