SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query optimization

    I have quite a big query which takes long time to bring the results. Can i please get help in indexing and optimization:

    SELECT * FROM contacts WHERE record_status!='Inactive' and record_status!='Deleted' and record_status!='nonmatured' and profile_sales_person1 IN(2,3,4,5,6) AND contact_first_name LIKE('aaaa%') AND contact_surname LIKE('bbbb%') AND (contact_number2 LIKE ('+23232323%') or contact_number2 LIKE('23232323%')) AND profile_status IN('Awaiting Finance (Not Pre Approved)','Cash Purchaser','Finance Purchaser (Pre Approved)') AND (profile_categories LIKE('%1%') OR profile_categories LIKE('%3%')) AND (contact_location LIKE('%Arabian Ranches%') OR contact_location LIKE('%DIFC%') OR contact_location LIKE('%Downtown%') OR contact_location LIKE('%DIP%') OR contact_location LIKE('%Marina%') OR contact_location LIKE('%Sports City%')) AND (contact_location LIKE('%Arabian Ranches%') OR contact_location LIKE('%DIFC%') OR contact_location LIKE('%Downtown%') OR contact_location LIKE('%DIP%') OR contact_location LIKE('%Marina%') OR contact_location LIKE('%Sports City%')) AND (villa_categories='Villa' OR apartment_categories='Apartment')

    Many thanks.

  2. #2
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,553
    Mentioned
    63 Post(s)
    Tagged
    0 Thread(s)
    I'm not an expert, and someone else will probably give you a better answer.

    But, at first glance, it looks like the reason for the poor performance is your extensive use of pattern matching - that is, the LIKE operator and wildcard characters. Example:

    contact_location LIKE('%Sports City%')

    Normally, to speed up a query, you need an index on the column that is being queried, which is contact_location in the above example. But that doesn't help when you are using pattern matching, because the query has to look in the relevant field in every row to see if it matches the pattern. If you knew that the contact location exactly matches 'Sports City', then you should use an equals operator rather than LIKE, and get rid of the wildcards. But presumably you can't do that in this case.

    You do have a couple of columns where you are testing for an exact match - in particular, record_status and profile_sales_person1. You would probably benefit from having an index on each of those columns.

    Mike

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Mikl View Post
    I'm not an expert, and someone else will probably give you a better answer.
    dude, don't sell yourself short, your answer was great


    Quote Originally Posted by Mikl View Post
    You do have a couple of columns where you are testing for an exact match - in particular, record_status and profile_sales_person1. You would probably benefit from having an index on each of those columns.
    it's worth a try but my money is on it not making a difference

    status and similar columns like gender are notoriously bad for indexing, as they contain too few values

    the most important thing i can recommend in a query of this nature is to make darned sure that your levels of parentheses are logical

    watch what happens when i simply reformat the query without changing it...
    Code:
    SELECT * 
      FROM contacts 
     WHERE record_status != 'Inactive' 
       and record_status != 'Deleted' 
       and record_status != 'nonmatured' 
       and profile_sales_person1 IN(2,3,4,5,6) 
       AND contact_first_name LIKE('aaaa%') 
       AND contact_surname LIKE('bbbb%') 
       AND (
           contact_number2 LIKE ('+23232323%') 
        or contact_number2 LIKE('23232323%')
           ) 
       AND profile_status IN('Awaiting Finance (Not Pre Approved)'
                            ,'Cash Purchaser'
                            ,'Finance Purchaser (Pre Approved)') 
       AND (
           profile_categories LIKE('%1%') 
        OR profile_categories LIKE('%3%')
           ) 
       AND (
           contact_location LIKE('%Arabian Ranches%') 
        OR contact_location LIKE('%DIFC%') 
        OR contact_location LIKE('%Downtown%') 
        OR contact_location LIKE('%DIP%') 
        OR contact_location LIKE('%Marina%') 
        OR contact_location LIKE('%Sports City%')
           ) 
      AND (
           contact_location LIKE('%Arabian Ranches%') 
        OR contact_location LIKE('%DIFC%') 
        OR contact_location LIKE('%Downtown%') 
        OR contact_location LIKE('%DIP%') 
        OR contact_location LIKE('%Marina%') 
        OR contact_location LIKE('%Sports City%')
           ) 
       AND (
           villa_categories = 'Villa' 
        OR apartment_categories = 'Apartment'
           )
    in general i would say that the parentheses that govern the ANDs and ORs are fine

    but did you notice anything else suspicious?

    also, i want to point out that LIKE is ~not~ a function, and you are not required to put the string in parentheses

    so i would change LIKE('%DIP%') to LIKE '%DIP%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Many thanks for the replies.


Bookmarks

Posting Permissions

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