# Optimizing a search query

**URL:** <https://forums.percona.com/t/optimizing-a-search-query/391>\
**Category:** Other MySQL® Questions\
**Created:** [July 25, 2007, 5:24am UTC](https://forums.percona.com/t/optimizing-a-search-query/391 "2007-07-25T05:24:28Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![kaahbonk](https://avatars.discourse-cdn.com/v4/letter/k/d2c977/32.png) [@kaahbonk](https://forums.percona.com/u/kaahbonk)\
**Post date:** [July 25, 2007, 5:24am UTC](https://forums.percona.com/t/optimizing-a-search-query/391/1 "2007-07-25T05:24:28Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [August 16, 2007, 7:37am UTC](https://forums.percona.com/t/optimizing-a-search-query/391/2 "2007-08-16T07:37:53Z")

</div>

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.