Query optimization

[Henson4004]

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.

[Mikl]

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

[r937]

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

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

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%'

[Henson4004]

Many thanks for the replies.