mysql - 提高 MySQL 查询性能

Question

在 PHP 文件中查询 ( SQL Fiddle )

SELECT tmdb_movies.movie_title,tmdb_movies.tmdb_id
,GROUP_CONCAT(DISTINCT videos.videos_key) as videos_key
,GROUP_CONCAT(DISTINCT videos.videos_name) as videos_name
,GROUP_CONCAT(DISTINCT genres.genres_name) AS genres_name

FROM tmdb_movies

LEFT JOIN videos ON videos.videos_tmdb_id=tmdb_movies.tmdb_id
JOIN genres USING (tmdb_id)

GROUP BY tmdb_movies.movie_title,tmdb_movies.tmdb_id

HAVING find_in_set('$category1', genres_name) $andor find_in_set('$category2', genres_name) 

ORDER BY $sortby $order LIMIT 10 OFFSET $start

在此查询中:$category1 和 $category2 是变量，例如( Action 、犯罪、恐怖、喜剧等)

$andor 是一个变量。如果变量 $category1 和 $category2 都有值，它的值为 AND。

如果只有$category1有值，则$andor变量值是OR

---

此处显示这 10 个查询大约需要 4-6 秒。我的预期速度小于 0.1 秒。我在 MySQL 表中有大约 8 万行和 35 列。

我的索引

create index idxm on tmdb_movies(tmdb_id);
create index idxv on videos(videos_tmdb_id, videos_name, videos_key);
create index idxv on genres(tmdb_id, genres_name);

tmdb_movies 表:

tmdb_id      movie_title
1            Logan
2            Iron Man
3            Superman

流派表

tmdb_id                 genres_name
1                         Crime
1                         Comedy
1                         Drama
2                         Action
2                         Horror
2                         Documentary
3                         Music

视频表

videos_tmdb_id          videos_name
1                       Official Trailer
1                       Trailer 2 
2                       Trailer 1
2                       Trailer 2 HD
3                       Superman Trailer 1
3                       Superman Trailer 2

如果您需要更多信息，请告诉我

编辑:解释查询截图

Answer 1

请尝试以下查询:

SELECT tmdb_movies.movie_title,tmdb_movies.tmdb_id
,GROUP_CONCAT(DISTINCT videos.videos_key) as videos_key
,GROUP_CONCAT(DISTINCT videos.videos_name) as videos_name
,GROUP_CONCAT(DISTINCT genres.genres_name) AS genres_name

FROM tmdb_movies

LEFT JOIN videos ON videos.videos_tmdb_id=tmdb_movies.tmdb_id
JOIN genres USING (tmdb_id)

WHERE 1=1
AND EXISTS( SELECT 1 FROM genres g WHERE g.tmdb_id = genres.tmdb_id AND g.genres_name = '$category1' )

GROUP BY tmdb_movies.movie_title,tmdb_movies.tmdb_id

ORDER BY $sortby $order LIMIT 10 OFFSET $start

请注意，我在 WHERE 子句中包含了条件 1=1。这样一来，您就可以轻松地附加 AND whatever。通过这种方式，您可以更轻松地在代码中构建 if 条件，以附加或不附加 $category2。像您一样检查空字符串是没有意义的，应该避免。

编辑:

要避免 GROUP_CONCAT 中的 DISTINCT，您必须将它们编写为子查询以避免通过联接进行乘法运算。不过，我没有为流派做这件事，因为我认为你的索引已经很好地涵盖了它。
无论如何，我也不得不说，我怀疑你会因此而获得任何改进。

SELECT tmdb_movies.movie_title,tmdb_movies.tmdb_id
, (SELECT GROUP_CONCAT(videos_key) FROM videos v WHERE v.videos_tmdb_id = tmdb_movies.tmdb_id)
, (SELECT GROUP_CONCAT(videos_name) FROM videos v WHERE v.videos_tmdb_id = tmdb_movies.tmdb_id)
,GROUP_CONCAT(DISTINCT genres.genres_name) AS genres_name

FROM tmdb_movies
JOIN genres USING (tmdb_id)

WHERE 1=1
AND EXISTS( SELECT 1 FROM genres g WHERE g.tmdb_id = genres.tmdb_id AND g.genres_name = '$category1' )

GROUP BY tmdb_movies.movie_title,tmdb_movies.tmdb_id

ORDER BY $sortby $order LIMIT 10 OFFSET $start