www.webdeveloper.com
Results 1 to 5 of 5

Thread: Potential problem in my query

  1. #1
    Join Date
    Jan 2005
    Posts
    592

    Potential problem in my query

    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.
    Code:
    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

  2. #2
    Join Date
    Dec 2007
    Location
    Dayton, OH
    Posts
    390
    Try doing an inner join instead of selecting from 2 tables.

  3. #3
    Join Date
    Jan 2005
    Posts
    592
    Not really sure how to transform that into an inner join one. Any help you can give?

  4. #4
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    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).

  5. #5
    Join Date
    Dec 2007
    Location
    Dayton, OH
    Posts
    390
    Quote Originally Posted by stephan.gerlach View Post
    Not really sure how to transform that into an inner join one. Any help you can give?
    Code:
    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles