mysql多个join_MySQL多重JOIN子句与多个WHERE

这是第3次编辑。基于您的所有反馈，我可以使用多个搜索条件生成以下查询。

请注意，这是一个现有的系统，并且预算有问题，所以我正在尽我所能改善现有的查询。您看到的搜索是基于数组手动完成的，并且没有连接。同样的搜索需要2-3分钟的时间来处理，而感谢所有摇滚大师，现在需要7-8秒的时间来处理:)

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我知道这可以通过将设置值中的查找移动到单独的表格并在联系人主表和值主表之间创建关系表来进一步改进。但正如我所说预算对这个家伙来说是一个大问题，所以我猜这对他们来说效率更高。

然而，任何进一步的改进想法都是受欢迎的