Potential problem in my query

[stephan.gerlach]

Hi there,

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, 
                         client.company_name,
                         client.address,
                         client.postcode 
                 FROM sites, 
                         client 
                 WHERE ( client.address LIKE "%lincolnshire%" 
                             OR (sites.address LIKE "%lincolnshire%" 
                                   AND sites.client=client.id 
                                   AND sites.deleted=0
                             )
                           )
                         AND client.type>-1 
                 GROUP BY client.id 
                 ORDER BY client.company_name ASC, client.id ASC

can anyone see anything dubious in this query?

Thanks for any help

[Kuriyama]

Try doing an inner join instead of selecting from 2 tables.

[stephan.gerlach]

Not really sure how to transform that into an inner join one. Any help you can give?

[svidgen]

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).

[Kuriyama]

Not really sure how to transform that into an inner join one. Any help you can give?

SELECT DISTINCT 
	client.id AS companyid, 
	client.company_name,
	client.address,
	client.postcode 
FROM 
	client 
INNER JOIN 
	sites 
ON
client.id = sites.client
WHERE 
	(client.address LIKE "%lincolnshire%" 
	OR sites.address LIKE "%lincolnshire%")
	AND sites.deleted=0	
	AND client.type > -1 
GROUP BY 
	client.id 
ORDER BY 
	client.company_name ASC, 
	client.id ASC

Hasn't been tested but give this a shot.