我的 Sql 查询非常慢（60秒），有人有改进的想法吗？

Question

我的查询很慢。我正在 3 张桌子之间搜索。 Se_movies = 80000 条记录，se_movie_contenttags=160 万条记录，se_movie_descriptiontags -200000 条记录

两个简化查询。

OR - 慢

SELECT SQL_NO_CACHE  count(movie_id)
FROM se_movies LEFT JOIN se_users ON se_movies.movie_user_id=se_users.user_id
LEFT  JOIN 
( 
  SELECT did , cid , ifnull(cid,did) as uid FROM ( SELECT tag_object_id AS did FROM se_movie_descriptiontags WHERE tag_name IN ('dog') ) AS dtag
  LEFT  JOIN (SELECT tag_object_id AS cid FROM se_movie_contenttags WHERE tag_name IN ('dog') ) AS ctag
  ON dtag.did=ctag.cid
UNION 
  SELECT did , cid , ifnull(cid,did) as uid  FROM ( SELECT tag_object_id AS did FROM se_movie_descriptiontags WHERE tag_name IN ('dog') ) AS dtag
  RIGHT  JOIN (SELECT tag_object_id AS cid FROM se_movie_contenttags WHERE tag_name IN ('dog') ) AS ctag
  ON dtag.did=ctag.cid
)
AS tagobjects ON se_movies.movie_id=tagobjects.uid
WHERE  ( se_movies.movie_title LIKE '%dog%') **OR**  (  (cid IS NOT NULL or  did IS NOT NULL) and uid IS NOT NULL )

AND - 快

SELECT SQL_NO_CACHE  count(movie_id)
FROM se_movies LEFT JOIN se_users ON se_movies.movie_user_id=se_users.user_id
LEFT  JOIN 
( 
  SELECT did , cid , ifnull(cid,did) as uid FROM ( SELECT tag_object_id AS did FROM se_movie_descriptiontags WHERE tag_name IN ('dog') ) AS dtag
  LEFT  JOIN (SELECT tag_object_id AS cid FROM se_movie_contenttags WHERE tag_name IN ('dog') ) AS ctag
  ON dtag.did=ctag.cid
UNION 
  SELECT did , cid , ifnull(cid,did) as uid  FROM ( SELECT tag_object_id AS did FROM se_movie_descriptiontags WHERE tag_name IN ('dog') ) AS dtag
  RIGHT  JOIN (SELECT tag_object_id AS cid FROM se_movie_contenttags WHERE tag_name IN ('dog') ) AS ctag
  ON dtag.did=ctag.cid
)
AS tagobjects ON se_movies.movie_id=tagobjects.uid
WHERE   ( se_movies.movie_title LIKE '%dog%') **AND**  (  (cid IS NOT NULL or  did IS NOT NULL) and uid IS NOT NULL )

每个人单独，

( se_movies.movie_title LIKE '%dog%') - around 300
 (cid IS NOT NULL or  did IS NOT NULL) and uid IS NOT NULL - return 595

但如果他们中的任何人返回大约 50 或更少，则速度很快。

基本上，如果任何关键字在每个标签和电影表标题中的值超过 500，则需要 60 秒，

否则需要 2.3 秒。

有更好的办法吗？

我知道你可以在这两者之间进行 UNION... 和 AND 对于其他东西...

搜索标题 OR ( contenttag 或 desctag )

问题是查询的变体太多...

如果数据位于两个不同的表中，执行 OR 是否有问题？无论如何要加快他们的速度？

谢谢。

系统 - mysql apache2 php5 ubuntu - 亚马逊 aws。

解释 在此处输入链接说明

已解决IT BY

SELECT SQL_NO_CACHE 计数(movie_id) FROM se_movies LEFT JOIN se_users ON se_movies.movi​​e_user_id=se_users.user_id 内连接 ( SELECT tag_object_id AS movieid FROM se_movie_contenttags WHERE tag_name IN ('dog') 联盟 选择 tag_object_id AS movieid FROM se_movie_descriptiontags WHERE tag_name IN ('dog') 联盟 SELECT s1.movi​​e_id AS movieid FROM se_movies as s1 WHERE s1.movi​​e_title LIKE '%dog%' 联盟 SELECT s2.movi​​e_id AS movieid FROM se_movies as s2 WHERE s2.movi​​e_desc LIKE '%dog%' ） AS tagobjects ON se_movies.movi​​e_id=tagobjects.movi

​​eid .5sec

Answer 1

如果 se_movie_descriptiontags 和 se_movie_contenttags 包含成对的 (movie_id, tag) 并且您想要包含具有特定标签的所有电影，我将替换 < code>tagobjects 内联视图：

SELECT `tag_object_id` AS `uid` FROM `se_movie_descriptiontags`
WHERE `tag_name` IN ('dog')
UNION
SELECT `tag_object_id` FROM `se_movie_contenttags`
WHERE `tag_name` IN ('dog')

此查询将返回在任何或两个 se_movie_descriptiontags 和 中具有标签“dog”的电影 ID se_movie_contenttags 表。然后您可以在主查询上删除 OR ((cid IS NOT NULL OR did IS NOT NULL) AND uid IS NOT NULL) 。

最终的完整查询将是：

SELECT SQL_NO_CACHE count(movie_id)
FROM
    se_movies LEFT JOIN
    se_users ON se_movies.movie_user_id=se_users.user_id INNER JOIN 
    ( 
        SELECT `tag_object_id` AS `uid` FROM `se_movie_descriptiontags`
        WHERE `tag_name` IN ('dog')
        UNION
        SELECT `tag_object_id` FROM `se_movie_contenttags`
        WHERE `tag_name` IN ('dog')
    ) AS tagobjects ON se_movies.movie_id=tagobjects.uid
WHERE (se_movies.movie_title LIKE '%dog%')

另外，请注意 (se_movies.movi​​e_title LIKE '%dog%') 本身就是一个性能杀手，因为它无法通过使用 上的索引来优化>movie_title 列，肯定会引起表扫描。这主要是因为条件以通配符开头。对于这种类型的场景，我建议研究 MySQL 的全文搜索功能。