mysql - 规范化使我的查询变慢

Question

在规范化之前，我有一个名为 genre 的列，它包含诸如“Action, Thriller, Comedy”之类的值

现在我已经通过创建 genre 和 movie2genre 表规范化了 genre 列。

现在的问题是我的查询更复杂而且实际上更慢

这两个查询基本上搜索 Action 片和惊悚片

旧查询

select title, genre from movie where genre like '%action%' and genre like '%thriller%'
0.062 sec duration / 0.032 sec fetch

新查询

SELECT movie.title, movie.genre
FROM Movie 
Where 
EXISTS (
 select *
 from movie2genre 
 JOIN Genre on Genre.id = movie2genre.GenreId 
 where Movie.id = movie2genre.MovieId 
   and genre in ('action', 'thriller')
)
0.328 sec duration / 0.078 sec fetch

我做错了什么吗？

更多信息:

电影

+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
| ID          | int(11)       | NO   | PRI | NULL    | auto_increment |
| Title       | varchar(345)  | YES  |     | NULL    |                |
  ETC....

类型

+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| genreid | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(50) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+.

movie2genre

+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| movieid | int(11) | YES  |     | NULL    |       |
| genreid | int(11) | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+

Answer 1

在没有相关查询的情况下尝试这个(如果您担心性能，请检查两个查询的执行计划)同时确保您的新表上有正确的索引。

 SELECT *
 FROM movie2genre mg, Genre g, Movie m
 WHERE m.id = mg.MovieId
 AND   g.id = mg.GenreId 
 AND   g.genre in ('action', 'thriller')