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

Matt Warden mwarden at gmail.com
Tue Feb 26 17:57:13 CST 2008

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.


Matt Warden
Cincinnati, OH, USA

This email proudly and graciously contributes to entropy.

More information about the thelist mailing list