SELECT SUM( IF( tag_id =1 || tag_id =5, 1, NULL ) ) AS tagmatches, movie . * , tag_id, DATE_FORMAT( movie.post_date, '%b %e, %Y' ) AS post_date, (
avg_con + avg_cre + avg_edi + avg_qul
) /4 AS avg, category.en AS categoryname, class.en AS classname
FROM movie
LEFT JOIN search_tags ON movie.id = search_tags.movie_id
LEFT JOIN search_tagwords ON tag_id = search_tagwords.id
LEFT JOIN category ON movie.category = category.id
LEFT JOIN class ON movie.class = class.id
WHERE (
search_tags.tag_id =1
OR search_tags.tag_id =5
)
OR (
(
tag_id IS NULL
OR (
search_tags.tag_id !=1
AND search_tags.tag_id !=5
)
)
AND (
movie.title
REGEXP '[[:<:]](pvp|warrior)[[:>:]]'
)
)
AND approved !=0
AND hide =0
GROUP BY movie.id
ORDER BY tagmatches DESC , movie.downloads DESC
LIMIT 0 , 5

EXPLAIN
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE movie index approved PRIMARY 3 NULL 13178 Using temporary; Using filesort
1 SIMPLE search_tags ref movie_id movie_id 3 wcm.movie.id 4 Using where
1 SIMPLE search_tagwords eq_ref PRIMARY PRIMARY 4 wcm.search_tags.tag_id 1 Using index
1 SIMPLE category eq_ref PRIMARY PRIMARY 3 wcm.movie.category 1
1 SIMPLE class eq_ref PRIMARY PRIMARY 3 wcm.movie.class 1