Hi,

currently I want to search (Dutch) postcodes using the following statement removing the possible white-space and case sensitivity

Quote:
where upper(replace(tbladdresses.zipcode,' ',''))='2279EK'



As the above 'where-statement' takes 10 seconds to return results, I wanted to speed-up this query by adding an additional clause making the new 'where statement':


Quote:
where tbladdresses.zipcode like '2279%' and upper(replace(tbladdresses.zipcode,' ',''))='2279EK'


Using only the first condition mysql performs fast; however adding the second condition makes it slow again.

Is there a way I can force mysql to prioritize the query on the first condition first, and then let mysql handle the second condition?

thanks

just a guess, but try this --

Code:
SELECT x.columns
FROM ( SELECT columns
FROM tbladdresses
WHERE zipcode like '2279%' ) AS x
WHERE UPPER(REPLACE(x.zipcode,' ','')) = '2279EK'

You could also think in the lines of removing white-spaces while "inserting" the data into the table. If you do that, you can do the "select" query without the "replace" function, making it much faster.