Potential problem in my query
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.
can anyone see anything dubious in this query?
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
Thanks for any help
Try doing an inner join instead of selecting from 2 tables.
Not really sure how to transform that into an inner join one. Any help you can give?
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).
Originally Posted by stephan.gerlach
Hasn't been tested but give this a shot.
client.id AS companyid,
client.id = sites.client
(client.address LIKE "%lincolnshire%"
OR sites.address LIKE "%lincolnshire%")
AND client.type > -1
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)