Suggestions on complex sql statement

SELECT `v`.*
  , GROUP_CONCAT(DISTINCT(`gg`.genre_id)) AS `genre_ids`
  , GROUP_CONCAT(DISTINCT(`gg`.genre_ru)) AS `genres`
  , GROUP_CONCAT(DISTINCT(`tg`.tag_id)) AS `tags_ids`
  , GROUP_CONCAT(DISTINCT(`tg`.name)) AS `tags`
FROM `video` AS `v`
LEFT JOIN `video_categories` AS `gc` ON gc.video_id = v.video_id
LEFT JOIN `video_genres` AS `gg` ON gg.genre_id = gc.genre_id
LEFT JOIN `tags_relations` AS `tr` ON tr.department=18 AND tr.parent_id = v.video_id
LEFT JOIN `tags` AS `tg` ON tg.tag_id = tr.tag_id
WHERE (v.video_id IN (
  SELECT gc2.video_id
  FROM video_categories gc2
  WHERE gc2.genre_id IN(44)
  GROUP BY gc2.video_id
  HAVING COUNT(gc2.video_id)=1)) 
AND (v.video_id IN (
  SELECT gc2.parent_id
  FROM 开发者_如何学编程tags_relations gc2
  WHERE gc2.tag_id IN(14) AND gc2.department=18
  GROUP BY gc2.parent_id
  HAVING COUNT(gc2.parent_id)=1))
GROUP BY `v`.`video_id`
ORDER BY `v`.`video_id` DESC

Query takes 0.4s~ to run. It's because of subquery in WHERE statement:

v.video_id IN (
    SELECT gc2.parent_id
    FROM tags_relations gc2
    WHERE gc2.tag_id IN(14) AND gc2.department=18
    GROUP BY gc2.parent_id
    HAVING COUNT(gc2.parent_id)=1
)

Without it, it runs 0.07s~

Maybe try something with advanced joins? shy

Thanks ;)

In MySQL, never use WHERE in (SELECT ... it is notoriously slow.

Use a join instead:

SELECT `v`.*
  , GROUP_CONCAT(DISTINCT(`gg`.genre_id)) AS `genre_ids`
  , GROUP_CONCAT(DISTINCT(`gg`.genre_ru)) AS `genres`
  , GROUP_CONCAT(DISTINCT(`tg`.tag_id)) AS `tags_ids`
  , GROUP_CONCAT(DISTINCT(`tg`.name)) AS `tags`
FROM video AS v
INNER JOIN video_categories AS gc ON gc.video_id = v.video_id 
  AND gc2.genre_id = '44'
LEFT JOIN video_genres AS gg ON gg.genre_id = gc.genre_id
LEFT JOIN tags_relations AS tr ON tr.department=18 AND tr.parent_id = v.video_id
LEFT JOIN tags AS tg ON tg.tag_id = tr.tag_id
INNER JOIN tags_relations tr2 ON (tr2.parent_id = v.video_id 
  AND tr2.tag_id = 14 AND tr2.department=18
GROUP BY `v`.`video_id`
ORDER BY `v`.`video_id` DESC

SELECT `v`.*
  , GROUP_CONCAT(DISTINCT(`gg`.genre_id)) AS `genre_ids`
  , GROUP_CONCAT(DISTINCT(`gg`.genre_ru)) AS `genres`
  , GROUP_CONCAT(DISTINCT(`tg`.tag_id)) AS `tags_ids`
  , GROUP_CONCAT(DISTINCT(`tg`.name)) AS `tags`
FROM `video` AS `v`
LEFT JOIN `video_categories` AS `gc` ON gc.video_id = v.video_id
LEFT JOIN `video_genres` AS `gg` ON gg.genre_id = gc.genre_id
LEFT JOIN `tags_relations` AS `tr` ON tr.department=18 AND tr.parent_id = v.video_id
LEFT JOIN `tags` AS `tg` ON tg.tag_id = tr.tag_id
JOIN (
  SELECT gc2.video_id
  FROM video_categories gc2
  WHERE gc2.genre_id IN(44)
  GROUP BY gc2.video_id
  HAVING COUNT(gc2.video_id)=1
  ) vc ON v.video_id = vc.video_id
JOIN (
  SELECT gc2.parent_id
  FROM tags_relations gc2
  WHERE gc2.tag_id IN(14) AND gc2.department=18
  GROUP BY gc2.parent_id
  HAVING COUNT(gc2.parent_id)=1
  ) tr ON v.video_id = tr.parent_id
GROUP BY `v`.`video_id`
ORDER BY `v`.`video_id` DESC

Edit:

I took a closer look at your query and think it can be reduced to the following:

SELECT `v1`.*
  , GROUP_CONCAT(DISTINCT(`gg`.genre_id)) AS `genre_ids`
  , GROUP_CONCAT(DISTINCT(`gg`.genre_ru)) AS `genres`
  , GROUP_CONCAT(DISTINCT(`tg`.tag_id)) AS `tags_ids`
  , GROUP_CONCAT(DISTINCT(`tg`.name)) AS `tags`
FROM (
SELECT v.*
FROM `video` AS `v`
LEFT JOIN `video_categories` AS `gc` ON gc.video_id = v.video_id
LEFT JOIN `tags_relations` AS `tr` ON tr.parent_id = v.video_id
WHERE gc.genre_id IN(44)
  AND tr.tag_id IN(14)
  AND tr.department=18
GROUP BY `v`.`video_id`
HAVING COUNT(*) = 1
) v1
LEFT JOIN `video_genres` AS `gg` ON gg.genre_id = gc.genre_id
LEFT JOIN `tags` AS `tg` ON tg.tag_id = tr.tag_id
GROUP BY v1.video_id
ORDER BY v1.video_id DESC