具有多个地方的MySQL多重联接子句

Question

这是第三版。基于您的所有反馈，我能够生成以下具有多个搜索条件的查询。

请注意，这是一个现有的系统和有预算是一个问题，所以我试图尽我所能改善现有的查询。您看到的搜索是基于数组手动完成的，并且没有连接。同样的搜索需要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

我知道，通过将find in set值移动到单独的表中，并在联系人母版表和值母版表之间创建关系表，可以进一步改进这一点。但正如我所说的，预算对这些人来说是个大问题，所以我想这对他们来说是非常有效率的。

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

Answer 1

主要假设你对某一特定地区或某一国家或某一特定城市的联系人感兴趣，并对你的代码进行一些清理：-

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

请注意，FIND_IN_SET的使用表明数据库的标准化程度很差，其中的字段包含逗号分隔的值列表。

Answer 2

这是在查询中出现错误的部分。

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

将此更改为

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'

编辑：您发布的查询是完全混乱的，您确实多次加入了同一个表，并且使用了链式OR条件而不是IN子句。下面是您修改过的查询。

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

Answer 3

当您有多个联接时，最好为表使用别名而不是全名。使阅读和理解更容易。无论如何，在第9行附近试一试，您将得到错误。

  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'