[thelist] [mysql] optimising query OR alternative to regexp?

[Matt Warden]

On 2/26/08, Paul Bennett <Paul.Bennett at wcc.govt.nz> wrote:
>  * db table with 100,000+ rows
>  * one 'name' field in the format 'lastname, first names'
>  * query like so:
>         select table1.somestuff, table2.otherstuff
>         from
>         table1, table2
>         where table1.date < 'somedate'
>         and table1.date > 'someotherdate'
>         and table1.name regexp '^[ a-z]{0,}smith[ a-z]{0,},[ a-z]{0,}$'
>         and table1.name regexp '^[ a-z]{0,},[ a-z]{0,}john[ a-z]{0,}$'
>         and table1.key = table2.foreignKey
...
>  I have indexes on the relevant columns (name, date).

Forget regex for a moment. Can you explain your requirement in
English. What exactly are you trying to search for? I have a feeling
you are searching for too much and in the process removing the
possibility for any index use.

Thanks,

-- 
Matt Warden
Cincinnati, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.