At my work we are running a custom written CRM system. Once a while the MySQL database seems to lock up. I suspect that there is a problem with the search for addresses.
This is the query that gets generated.
SELECT DISTINCT client.id as companyid,
WHERE ( client.address LIKE "%lincolnshire%"
OR (sites.address LIKE "%lincolnshire%"
GROUP BY client.id
ORDER BY client.company_name ASC, client.id ASC
At first glance, it doesn't appear as though you're doing anything too terribly complex. Though, I would [try to] do two things differently:
Force a left join, using client as the left-hand table.
This ought to force MySQL into performing this query in a more intelligent manner. Of course, the only way to know for sure is to benchmark the difference ...
Avoid using wildcards at the beginning of your match.
Putting a wildcard at the beginning of a LIKE match completely bypasses your indexes (which I assume you have), forcing a full table scan (or two). So, assuming you don't want to lose out on your ability to match anywhere in a string, you might want to consider using FULLTEXT indexes on those fields (or build your own "fulltext" index table).
Not really sure how to transform that into an inner join one. Any help you can give?
client.id AS companyid,
client.id = sites.client
(client.address LIKE "%lincolnshire%"
OR sites.address LIKE "%lincolnshire%")
AND client.type > -1
Hasn't been tested but give this a shot.
It's truly shameful that I have to tell people that they are asking .NET questions in a classic ASP board. . .