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

[Paul Bennett]

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