regex - MySQL using REGEXP causing long query times -

i understand using regexp has drawbacks, when comes query times, however, have no other option, except use it.

the problem is, queries can take on 5 minutes run, depending on how many search terms used.

my task take search term, , find exact search term, , variations of word, such plural, ending in 'ing', or search term followed punctuation, not fragments of words, "car" should not match "carbine" or "scar". infinite number of search terms can used, when number starts above 6, becomes unbearably long.

here sample of query:

select `id` `table` ((      `name`  regexp "[[:<:]]sesame street[[:>:]]" or     `name`  regexp "sesame street[[:punct:]]" or     `name`  regexp "[[:<:]]sesame street.?ing[[:>:]]" or     `name`  regexp "[[:<:]]sesame street.?s[[:>:]]" ) or (      `venue`  regexp "[[:<:]]disney[[:>:]]" or     `venue`  regexp "disney[[:punct:]]" or     `venue`  regexp "[[:<:]]disney.?ing[[:>:]]" or     `venue`  regexp "[[:<:]]disney.?s[[:>:]]"  ))   , `name` not "% tantrum %"   , `name` not "% stepkids %"   , `date` >= curdate()   order `date` asc; 

the query uses single table, there no table join issues.

a single query contain 30 different keywords either include, or exclude, , there variations of search terms, each term create 5 different conditionals. since query uses regexp, index can used date field, although indexes set on name , venue field. have thought changing search engine use solr instead, then, not think work, given specificity search requires.

any advice appreciated. thanks

you achieve improvement (up factor of 4) using single expression per search term instead of 4 have now.

instead of using:

`venue`  regexp "[[:<:]]disney[[:>:]]" or `venue`  regexp "disney[[:punct:]]" or `venue`  regexp "[[:<:]]disney.?ing[[:>:]]" or `venue`  regexp "[[:<:]]disney.?s[[:>:]]" 

you write:

`venue`  regexp "[[:<:]]disney(.?(s|ing))?[[:>:]]" 

you don't need "disney[[:punct:]]" [[:>:]] match between words , punctuation.