[thelist] [mysql] optimising query OR alternative to regexp?
Paul Bennett
Paul.Bennett at wcc.govt.nz
Tue Feb 26 16:46:03 CST 2008
Hi all,
* 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
Though it returns good quality results, over a large date range (like 100 years), the regexp seems to make the query really slow. I undertsand the query processor is doing a lot of work, but any ideas on how to speed it up?
I have indexes on the relevant columns (name, date).
??
Paul
More information about the thelist
mailing list