Início / dba / Perguntas / 16352
Accepted
Josh
Asked: 2012-04-12 06:47:21 +0800 CST

Por que a consulta a seguir não está usando o índice `always`?

  • 772

Esta manhã comecei a receber avisos de carga em um dos servidores MySQL que gerencio. SHOW FULL PROCESSLISTrevelou que o culpado foi a seguinte consulta (mal escrita):

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

Não tenho certeza de quem escreveu isso. Parece que está em um banco de dados que desenvolvi há mais de 5 anos, no qual pessoas desconhecidas têm mexido bastante desde a última vez que o toquei. As tabelas em questão são:

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;

Quando fui alertado pela primeira vez para esta situação, movies.always nem sequer tinha um índice , nem tinha movies.listorder. Eu tinha certeza de que esse era o problema, no entanto, adicionar um índice nessas duas colunas não alterou o 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)

Eu tentei reescrever a consulta da seguinte maneira:

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

No entanto EXPLAIN, não foi diferente. Por que essa consulta não está usando um índice na moviestabela? Como posso explicar aos desenvolvedores que eles devem reescrever esta consulta para que ela não prejudique o servidor?

mysql index

2 respostas

  1. Best Answer

    Pode ser possível reescrevê-lo em umUNION

    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

    Eu não testei o acima, mas estou curioso para saber os resultados doEXPLAIN

    Seu alwaysatributo está falhando de duas maneiras:

    1. É uma seletividade muito baixa como aponta o @ypercube, pois existem apenas dois valores possíveis. O MySQL descartará usando esses tipos de índices

    2. A consulta tem OR. O que significa que teria que fazer uma varredura de tabela de qualquer maneira para obter a correspondência de ambas as condições.

    • 2

  2. Você também pode tentar isso (evitando o grupo por):

    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
    • 0

relate perguntas