Optimizing a search query

MySQL & MariaDB Other MySQL® Questions
1

I need help optimizing this search query. It takes anything from 3s up to 10s to execute it.

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 classnameFROM movieLEFT JOIN search_tags ON movie.id = search_tags.movie_idLEFT JOIN search_tagwords ON tag_id = search_tagwords.idLEFT JOIN category ON movie.category = category.idLEFT JOIN class ON movie.class = class.idWHERE (search_tags.tag_id =1OR search_tags.tag_id =5)OR ((tag_id IS NULLOR (search_tags.tag_id !=1AND search_tags.tag_id !=5))AND (movie.titleREGEXP ‘[:<:][[:>:]]’))AND approved !=0AND hide =0GROUP BY movie.idORDER BY tagmatches DESC , movie.downloads DESCLIMIT 0 , 5

EXPLAINid select_type table type possible_keys key key_len ref rows Extra1 SIMPLE movie index approved PRIMARY 3 NULL 13178 Using temporary; Using filesort1 SIMPLE search_tags ref movie_id movie_id 3 wcm.movie.id 4 Using where1 SIMPLE search_tagwords eq_ref PRIMARY PRIMARY 4 wcm.search_tags.tag_id 1 Using index1 SIMPLE category eq_ref PRIMARY PRIMARY 3 wcm.movie.category 11 SIMPLE class eq_ref PRIMARY PRIMARY 3 wcm.movie.class 1

Any advice/help offered will be much appreciated )
Thanks!

2

Simple answer would be

  1. Do not use regexp for search - it can’t be indexed. Look at MySQL Full text search or external tools like Sphinx

  2. Denormalize - Joins are expensive.