1

This is the 3rd edit. Based on all your feedback I was able to generate the following query with multiple search criteria.

Please note that this is an existing system and there budget is an issue so I am trying to do all I can to improve existing queries. The search you see was manually done based on arrays and there was no joins. The same search was taking 2-3 minutes to process whereas thanks to all of you rocking gurus it now takes 7-8 seconds to process :)

SELECT SQL_CALC_FOUND_ROWS fname, lname, desig, company, region, state, country, add_uid, contacts.`id` as id
        FROM contacts
         INNER JOIN contact_to_categories ON contact_to_categories.contactid = contacts.id 
 AND ( 
contact_to_categories.catid = '2'
 ) 

 INNER JOIN contact_professional_details ON contact_professional_details.contact_id = contacts.id
 AND ( 
FIND_IN_SET('1', contact_professional_details.pd_insid)
 OR FIND_IN_SET(' 8', contact_professional_details.pd_insid)
 OR FIND_IN_SET(' 33', contact_professional_details.pd_insid)
 ) 

 AND ( 
FIND_IN_SET('4', contact_professional_details.pd_secid)
 OR FIND_IN_SET('3', contact_professional_details.pd_secid)
 OR FIND_IN_SET('5', contact_professional_details.pd_secid)
 OR FIND_IN_SET('7', contact_professional_details.pd_secid)
 OR FIND_IN_SET('12', contact_professional_details.pd_secid)
 OR FIND_IN_SET('11', contact_professional_details.pd_secid)
 OR FIND_IN_SET('9', contact_professional_details.pd_secid)
 OR FIND_IN_SET('38', contact_professional_details.pd_secid)
 OR FIND_IN_SET('35', contact_professional_details.pd_secid)
 OR FIND_IN_SET('115', contact_professional_details.pd_secid)
 ) 

 INNER JOIN contact_address ON contact_address.contact_id = contacts.id
 AND ( 
contact_address.hmregion IN ('AF', 'EU', 'OC', 'SA')
 OR contact_address.hmcountry IN ('Algeria', 'Angola', 'Benin', 'Comoros', 'Andorra', 'Austria', 'Belarus', 'Belgium', 'American Samoa', 'Australia', 'French Polynesia', 'Guam', 'Kiribati', 'Marshall Islands', 'Colombia', 'Ecuador', 'Falkland Islands', 'Guyana', 'Paraguay', 'Peru', 'Laos', 'Malaysia', 'Myanmar', 'Singapore', 'Vietnam')
 OR contact_address.hmcity = 'singapore'
 ) 

 INNER JOIN contact_offices ON contact_offices.contact_id = contacts.id
 AND ( 
contact_offices.off_region IN ('AF', 'EU', 'OC', 'SA')
 OR contact_offices.off_country IN ('Algeria', 'Angola', 'Benin', 'Comoros', 'Andorra', 'Austria', 'Belarus', 'Belgium', 'American Samoa', 'Australia', 'French Polynesia', 'Guam', 'Kiribati', 'Marshall Islands', 'Colombia', 'Ecuador', 'Falkland Islands', 'Guyana', 'Paraguay', 'Peru', 'Laos', 'Malaysia', 'Myanmar', 'Singapore', 'Vietnam')
 OR contact_offices.off_city = 'singapore'
 ) 


        WHERE 1 AND ( 
FIND_IN_SET('1', contacts.ins_id)
 OR FIND_IN_SET(' 8', contacts.ins_id)
 OR FIND_IN_SET(' 33', contacts.ins_id)
 )

 AND ( 
FIND_IN_SET('4', contacts.sec_id)
 OR FIND_IN_SET('3', contacts.sec_id)
 OR FIND_IN_SET('5', contacts.sec_id)
 OR FIND_IN_SET('7', contacts.sec_id)
 OR FIND_IN_SET('12', contacts.sec_id)
 OR FIND_IN_SET('11', contacts.sec_id)
 OR FIND_IN_SET('9', contacts.sec_id)
 OR FIND_IN_SET('38', contacts.sec_id)
 OR FIND_IN_SET('35', contacts.sec_id)
 OR FIND_IN_SET('115', contacts.sec_id)
 )

 AND ( FIND_IN_SET('Tier 1', `vip_tier`) OR FIND_IN_SET('Tier 3', `vip_tier`) )
 AND ( FIND_IN_SET('Tier A', `vip_coll_tier`) )
 AND ( FIND_IN_SET('Yes', `vip_influencer`) )
 AND ( FIND_IN_SET('Contemporary', `vip_class_art_coll`) OR FIND_IN_SET('Modern', `vip_class_art_coll`) OR FIND_IN_SET('Geographic', `vip_class_art_coll`) )
 AND ( FIND_IN_SET('Sculpture', `vip_med_art_coll`) OR FIND_IN_SET('Photography', `vip_med_art_coll`) OR FIND_IN_SET('Video', `vip_med_art_coll`) OR FIND_IN_SET('Installation', `vip_med_art_coll`) )
 AND ( FIND_IN_SET('Japan', `vip_geo_int`) OR FIND_IN_SET('Korea', `vip_geo_int`) OR FIND_IN_SET('Southeast Asia', `vip_geo_int`) OR FIND_IN_SET('Oceania', `vip_geo_int`) )
 AND ( FIND_IN_SET('HNWI', `vip_seniority`) OR FIND_IN_SET('Top Social Leaders', `vip_seniority`) OR FIND_IN_SET('Other Executives', `vip_seniority`) )
 AND ( `status` = 'a' )

        ORDER BY  fname
                    asc
        LIMIT 0, 50

I know this can be further improved by moving the find in set values to separate tables and creating relational tables between the contacts master table and the values master table. But as I said budget is a big issue for this guys so I guess this is more than efficient for them.

However any further improvement ideas are most welcome.

Improve this question
8
  • 2
    You have a load of extra brackets and some redundant AND in the ON clauses. Commented May 30, 2014 at 9:05
  • 1
    Use an IN clause to reduce the huge list of OR conditions that you have. Commented May 30, 2014 at 9:07
  • 3
    ...and normalize your design. Seriously. Commented May 30, 2014 at 9:09
  • Please remove extra code:from line#9 to line#17 INNER JOIN contact_to_categories ON contact_to_categories.contactid = contacts.id AND ( contact_to_categories.catid = '2' ) INNER JOIN contact_professional_details ON contact_professional_details.contact_id = contacts.id AND ( ) INNER JOIN contact_professional_details ON contact_professional_details.contact_id = contacts.id AND ( Commented May 30, 2014 at 9:13
  • Honestly, I don't see how this query is going to work at all... Commented May 30, 2014 at 9:16

4 Answers 4

Reset to default
4

This is the part which giving error in your query

INNER JOIN contact_professional_details 
ON contact_professional_details.contact_id = contacts.id
 AND (       <-- Here
INNER JOIN contact_to_categories 
ON contact_to_categories.contactid = contacts.id 
 AND (
contact_to_categories.catid = '2'
)

change this to

INNER JOIN contact_professional_details 
ON contact_professional_details.contact_id = contacts.id

INNER JOIN contact_to_categories 
ON contact_to_categories.contactid = contacts.id 
 AND contact_to_categories.catid = '2'

EDIT: Your posted query is total messy, you did join the same table(s) multiple times and did use chained OR condition instead of IN clause. So, below is your modified query.

SELECT SQL_CALC_FOUND_ROWS fname, 
lname, 
desig, 
company, 
region, 
state, 
country, 
add_uid, 
contacts.`id` as id
FROM contacts

INNER JOIN contact_to_categories 
ON contact_to_categories.contactid = contacts.id 
AND contact_to_categories.catid = '2'

INNER JOIN contact_professional_details 
ON contact_professional_details.contact_id = contacts.id
 AND (
 FIND_IN_SET('4', contact_professional_details.pd_secid)
 OR FIND_IN_SET('3', contact_professional_details.pd_secid)
 OR FIND_IN_SET('5', contact_professional_details.pd_secid)
 OR FIND_IN_SET('7', contact_professional_details.pd_secid)
 OR FIND_IN_SET('12', contact_professional_details.pd_secid)
 OR FIND_IN_SET('11', contact_professional_details.pd_secid)
 OR FIND_IN_SET('9', contact_professional_details.pd_secid)
 OR FIND_IN_SET('38', contact_professional_details.pd_secid)
 OR FIND_IN_SET('35', contact_professional_details.pd_secid)
 OR FIND_IN_SET('115', contact_professional_details.pd_secid)
)

 INNER JOIN contact_address ON contact_address.contact_id = contacts.id
 AND 
contact_address.hmregion IN ('AF','EU','OC','SA')
AND 
contact_address.hmcountry IN ('Algeria',
'Angola',
'Benin',
'Comoros',
'Andorra',
'Austria',
'Belarus',
'Belgium',
'American Samoa',
'Australia',
'French Polynesia',
'Guam',
'Kiribati',
'Marshall Islands',
'Colombia',
'Ecuador',
'Falkland Islands',
'Guyana',
'Paraguay',
'Peru',
'Laos',
'Malaysia',
'Myanmar',
'Singapore',
'Vietnam'
)
AND contact_address.hmcity='singapore'

 INNER JOIN contact_offices ON contact_offices.contact_id = contacts.id
 AND 
contact_offices.off_region IN ('AF','EU','OC','SA')
AND
contact_offices.off_country IN ('Algeria',
 'Angola',
 'Benin',
 'Comoros',
 'Andorra',
 'Austria',
 'Belarus',
 'Belgium',
 'American Samoa',
 'Australia',
 'French Polynesia',
 'Guam',
 'Kiribati',
 'Marshall Islands',
 'Colombia',
 'Ecuador',
 'Falkland Islands',
 'Guyana',
 'Paraguay',
 'Peru',
 'Laos',
 'Malaysia',
 'Myanmar',
 'Singapore',
 'Vietnam'
)
AND contact_offices.off_city='singapore'



WHERE 1 AND (
FIND_IN_SET('1', contacts.ins_id)
 OR FIND_IN_SET(' 8', contacts.ins_id)
 OR FIND_IN_SET(' 33', contacts.ins_id)
 )

 AND (
FIND_IN_SET('4', contacts.sec_id)
 OR FIND_IN_SET('3', contacts.sec_id)
 OR FIND_IN_SET('5', contacts.sec_id)
 OR FIND_IN_SET('7', contacts.sec_id)
 OR FIND_IN_SET('12', contacts.sec_id)
 OR FIND_IN_SET('11', contacts.sec_id)
 OR FIND_IN_SET('9', contacts.sec_id)
 OR FIND_IN_SET('38', contacts.sec_id)
 OR FIND_IN_SET('35', contacts.sec_id)
 OR FIND_IN_SET('115', contacts.sec_id)
 )

 AND (FIND_IN_SET('Tier 1', `vip_tier`) OR FIND_IN_SET('Tier 3', `vip_tier`))
 AND (FIND_IN_SET('Tier A', `vip_coll_tier`))
 AND (FIND_IN_SET('Yes', `vip_influencer`))
 AND (FIND_IN_SET('Contemporary', `vip_class_art_coll`) OR FIND_IN_SET('Modern', `vip_class_art_coll`) OR FIND_IN_SET('Geographic', `vip_class_art_coll`))
 AND (FIND_IN_SET('Sculpture', `vip_med_art_coll`) OR FIND_IN_SET('Photography', `vip_med_art_coll`) OR FIND_IN_SET('Video', `vip_med_art_coll`) OR FIND_IN_SET('Installation', `vip_med_art_coll`))
 AND (FIND_IN_SET('Japan', `vip_geo_int`) OR FIND_IN_SET('Korea', `vip_geo_int`) OR FIND_IN_SET('Southeast Asia', `vip_geo_int`) OR FIND_IN_SET('Oceania', `vip_geo_int`))
 AND (FIND_IN_SET('HNWI', `vip_seniority`) OR FIND_IN_SET('Top Social Leaders', `vip_seniority`) OR FIND_IN_SET('Other Executives', `vip_seniority`))
 AND (`status`='a')

ORDER BY  fname asc
LIMIT 0,50
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks for your kind help. Will try to adjust the query generator now and let you guys know soon.
Thanks a lot for your help. Appreciate it!
@Kunal, then it's time to accept the answer and Welcome.
3

When you have multiple joins, its better to use alias for your tables instead of the whole name. Makes reading and understanding much easier. Anyway try this near line 9 where you get the error. 

  INNER JOIN contact_professional_details
    ON contact_professional_details.contact_id = contacts.id
    INNER JOIN contact_to_categories ON contact_to_categories.contactid = contacts.id 
    AND contact_to_categories.catid = '2'
Improve this answer

Comments

2

Your inner join conditions are not correct.

For example 

INNER JOIN contact_to_categories ON contact_to_categories.contactid = contacts.id
AND
(
    contact_to_categories.catid = '2'
)

needs to be

INNER JOIN contact_to_categories ON (
        contact_to_categories.contactid = contacts.id
    AND contact_to_categories.catid = '2'
)

You will need to fix all the inner joins you have that follow the aforementioned pattern

Improve this answer

5 Comments

Ok noted. Will try correcting the conditions as suggested by @geoand and add table alias names. Will post result soon. Thanks a tonne gurus.
While the original code is messy like this, the brackets in odd places like this will have no effect on how the code works here. But the bits like INNER JOIN contact_professional_details ON contact_professional_details.contact_id = contacts.id AND ( INNER JOIN contact_to_categories ON contact_to_categories.contactid = contacts.id AND ( contact_to_categories.catid = '2' ) where there is an extra AND ( (and no matching closing bracket) will cause major problems
@geoand Thanks for the help gurus. Kudos to your knowledge :)
@Kickstart I changed the bracket positions and added alias names. Editing my main question now. The query now runs perfectly but there is a minor problem. Pls see the edited question above.
Hi I am having some problem with another query. Would be great if you can help.
1

Making a major assumption that you are interested in contacts in a specified region OR a specified country OR a specified city, and cleaning your code up a bit:-

SELECT SQL_CALC_FOUND_ROWS fname, lname, desig, company, region, state, country, add_uid, contacts.`id` as id
FROM contacts
INNER JOIN contact_to_categories ON contact_to_categories.contactid = contacts.id AND contact_to_categories.catid = '2'
INNER JOIN contact_professional_details ON contact_professional_details.contact_id = contacts.id
AND (
 FIND_IN_SET('4', contact_professional_details.pd_secid)
 OR FIND_IN_SET('3', contact_professional_details.pd_secid)
 OR FIND_IN_SET('5', contact_professional_details.pd_secid)
 OR FIND_IN_SET('7', contact_professional_details.pd_secid)
 OR FIND_IN_SET('12', contact_professional_details.pd_secid)
 OR FIND_IN_SET('11', contact_professional_details.pd_secid)
 OR FIND_IN_SET('9', contact_professional_details.pd_secid)
 OR FIND_IN_SET('38', contact_professional_details.pd_secid)
 OR FIND_IN_SET('35', contact_professional_details.pd_secid)
 OR FIND_IN_SET('115', contact_professional_details.pd_secid)
)
INNER JOIN contact_address ON contact_address.contact_id = contacts.id 
INNER JOIN contact_offices ON contact_offices.contact_id = contacts.id 
WHERE 1 
AND ((
    contact_address.hmregion IN ('AF', 'EU', 'OC', 'SA')
    OR contact_address.hmcountry IN ('Algeria', 'Angola', 'Benin', 'Comoros', 'Andorra', 'Austria', 'Belarus', 'Belgium', 'American Samoa', 'Australia', 'French Polynesia', 'Guam', 'Kiribati', 'Marshall Islands', 'Colombia', 'Ecuador', 'Falkland Islands', 'Guyana', 'Paraguay', 'Peru', 'Laos', 'Malaysia', 'Myanmar', 'Singapore', 'Vietnam')
    OR contact_address.hmcity='singapore'
)
OR (
    contact_offices.off_region IN ('AF', 'EU', 'OC', 'SA')
    OR contact_offices.off_country IN ('Algeria', 'Angola', 'Benin', 'Comoros', 'Andorra', 'Austria', 'Belarus', 'Belgium', 'American Samoa', 'Australia', 'French Polynesia', 'Guam', 'Kiribati', 'Marshall Islands', 'Colombia', 'Ecuador', 'Falkland Islands', 'Guyana', 'Paraguay', 'Peru', 'Laos', 'Malaysia', 'Myanmar', 'Singapore', 'Vietnam')
    OR contact_offices.off_city='singapore'
)
)
AND (
    FIND_IN_SET('1', contacts.ins_id)
    OR FIND_IN_SET(' 8', contacts.ins_id)
    OR FIND_IN_SET(' 33', contacts.ins_id)
)
AND (
    FIND_IN_SET('4', contacts.sec_id)
    OR FIND_IN_SET('3', contacts.sec_id)
    OR FIND_IN_SET('5', contacts.sec_id)
    OR FIND_IN_SET('7', contacts.sec_id)
    OR FIND_IN_SET('12', contacts.sec_id)
    OR FIND_IN_SET('11', contacts.sec_id)
    OR FIND_IN_SET('9', contacts.sec_id)
    OR FIND_IN_SET('38', contacts.sec_id)
    OR FIND_IN_SET('35', contacts.sec_id)
    OR FIND_IN_SET('115', contacts.sec_id)
)
AND (
    FIND_IN_SET('Tier 1', `vip_tier`) 
    OR FIND_IN_SET('Tier 3', `vip_tier`)
 )
AND (FIND_IN_SET('Tier A', `vip_coll_tier`))
AND (FIND_IN_SET('Yes', `vip_influencer`))
AND (FIND_IN_SET('Contemporary', `vip_class_art_coll`) OR FIND_IN_SET('Modern', `vip_class_art_coll`) OR FIND_IN_SET('Geographic', `vip_class_art_coll`))
AND (FIND_IN_SET('Sculpture', `vip_med_art_coll`) OR FIND_IN_SET('Photography', `vip_med_art_coll`) OR FIND_IN_SET('Video', `vip_med_art_coll`) OR FIND_IN_SET('Installation', `vip_med_art_coll`))
AND (FIND_IN_SET('Japan', `vip_geo_int`) OR FIND_IN_SET('Korea', `vip_geo_int`) OR FIND_IN_SET('Southeast Asia', `vip_geo_int`) OR FIND_IN_SET('Oceania', `vip_geo_int`))
AND (FIND_IN_SET('HNWI', `vip_seniority`) OR FIND_IN_SET('Top Social Leaders', `vip_seniority`) OR FIND_IN_SET('Other Executives', `vip_seniority`))
AND (`status`='a')
ORDER BY  fname asc
LIMIT 0, 50

Note that the use of FIND_IN_SET suggests a poorly normalised database with fields containing comma separated lists of values.

Improve this answer

2 Comments

Have edited the main body. The query works fine now. Any further suggestions are welcome. Thanks a lot for your help. Cheers!
You appear to have pretty much updated your original question with my suggested query. While it can be improved this would likely need a database redesign. For example you use FIND_IN_SET against contacts.sec_id 10 times. FIND_IN_SET is not that efficient and it would be FAR more efficient to move the comma separated list of values out of contacts.sec_id and instead put them into another table with one row per contact per id. Then checking for a value could use indexes. The same applies to most other times you are using FIND_IN_SET.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.