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.