Re: Optimizing query

[Scott Nemes]

I can't say if this will be faster or not without having the actual data to test it on, but it's worth a shot. Basic idea is you use group_concat to pool all the statuses together and then check the resulting string for what you want to include/exclude:

select
group_concat(status) as `statusComb`,
name,
surname,
address
from <table>
where city = 'Detroit'
group by name,surname,address
having (statusComb like "%owner%" or statusComb like "%renting%")
and statusComb not like "%evicted%"
and statusComb not like "%moved%";

--
Scott Nemes
MySQL DBA

http://www.linkedin.com/in/scottnemes
http://www.twitter.com/ScottNemes