MySQL 多重内部连接、联合和 where 子句

Question

编辑到我原来​​的帖子。使用 LEFT join 后，查询的外观如下。

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

         LEFT JOIN contact_art_collections ON contact_art_collections.contact_id = contacts.id
         AND (
             contact_art_collections.name LIKE '%singapore%'
             OR contact_art_collections.email LIKE '%singapore%'
             OR contact_art_collections.ph1_no LIKE '%singapore%'
             OR contact_art_collections.ph1_desc LIKE '%singapore%'
             OR contact_art_collections.ph2_no LIKE '%singapore%'
             OR contact_art_collections.ph2_desc LIKE '%singapore%'
             OR contact_art_collections.web LIKE '%singapore%'
             OR contact_art_collections.address LIKE '%singapore%'
         )

 LEFT JOIN contact_offices ON contact_offices.contact_id = contacts.id
 AND(
    contact_offices.off_address LIKE '%singapore%'
    OR contact_offices.off_phone1 LIKE '%singapore%'
    OR contact_offices.off_phone1_desc LIKE '%singapore%'
    OR contact_offices.off_phone2 LIKE '%singapore%'
    OR contact_offices.off_phone2_desc LIKE '%singapore%'
    OR contact_offices.off_phone3 LIKE '%singapore%'
    OR contact_offices.off_phone3_desc LIKE '%singapore%'
    OR contact_offices.off_city LIKE '%singapore%'
    OR contact_offices.off_zip LIKE '%singapore%'
    OR contact_offices.off_state LIKE '%singapore%'
    OR contact_offices.off_region LIKE '%singapore%'
    OR contact_offices.off_country LIKE '%singapore%'
 )

 LEFT JOIN contact_professional_details ON contact_professional_details.contact_id = contacts.id
 AND(
    contact_professional_details.pd_desig LIKE '%singapore%'
    OR contact_professional_details.pd_comp LIKE '%singapore%'
    OR contact_professional_details.pd_regd_comp LIKE '%singapore%'
 )

 LEFT JOIN contact_address ON contact_address.contact_id = contacts.id
 AND(
    contact_address.hmadd LIKE '%singapore%'
    OR contact_address.hmcity LIKE '%singapore%'
    OR contact_address.hmzip LIKE '%singapore%'
    OR contact_address.hmstate LIKE '%singapore%'
    OR contact_address.hmregion LIKE '%singapore%'
    OR contact_address.hmcountry LIKE '%singapore%'
 )

 LEFT JOIN contact_emails ON contact_emails.contact_id = contacts.id
 AND(
     contact_emails.email LIKE '%singapore%'
 )

 LEFT JOIN assistant_emails ON assistant_emails.contact_id = contacts.id
 AND(
     assistant_emails.email LIKE '%singapore%'
 )

 LEFT JOIN contact_fax ON contact_fax.contact_id = contacts.id
 AND(
    contact_fax.fax LIKE '%singapore%'
 )

 LEFT JOIN contact_phones ON contact_phones.contact_id = contacts.id
 AND(
     contact_phones.ph_no LIKE '%singapore%'
     OR contact_phones.ph_desc LIKE '%singapore%' 
 )

 LEFT JOIN assistant_phones ON assistant_phones.contact_id = contacts.id
 AND(
     assistant_phones.ph_no LIKE '%singapore%' 
     OR assistant_phones.ph_desc LIKE '%singapore%' 
 )

 LEFT JOIN contact_websites ON contact_websites.contact_id = contacts.id
 AND(
     contact_websites.web LIKE '%singapore%'
 )

        WHERE ( contacts.`title` LIKE '%singapore%' OR 
contacts.`fname` LIKE '%singapore%' OR 
contacts.`lname` LIKE '%singapore%' OR 
contacts.`full_name` LIKE '%singapore%' OR 
contacts.`desig` LIKE '%singapore%' OR 
contacts.`company` LIKE '%singapore%' OR 
contacts.`regd_company` LIKE '%singapore%' OR 
contacts.`remarks` LIKE '%singapore%' OR 
contacts.`artstage_contact` LIKE '%singapore%' OR 
contacts.`status` LIKE '%singapore%' OR 
contacts.`referred_by_name` LIKE '%singapore%' OR 
contacts.`vip_tier` LIKE '%singapore%' OR 
contacts.`vip_coll_tier` LIKE '%singapore%' OR 
contacts.`vip_influencer` LIKE '%singapore%' OR 
contacts.`vip_seniority` LIKE '%singapore%' OR 
contacts.`vip_as_fname` LIKE '%singapore%' OR 
contacts.`vip_as_lname` LIKE '%singapore%' OR 
contacts.`vip_as_email` LIKE '%singapore%' OR 
contacts.`vip_as_ph` LIKE '%singapore%' OR 
contacts.`vip_class_art_coll` LIKE '%singapore%' OR 
contacts.`vip_med_art_coll` LIKE '%singapore%' OR 
contacts.`vip_geo_int` LIKE '%singapore%' OR 
contacts.`media_art_media` LIKE '%singapore%' OR 
contacts.`media_freq` LIKE '%singapore%' OR 
contacts.`exb_waitlist` LIKE '%singapore%' OR 
contacts.`exb_blacklist` LIKE '%singapore%' OR 
contacts.`exb_greylist` LIKE '%singapore%' OR 
contacts.`exb_grade` LIKE '%singapore%' OR 
contacts.`exb_art_fairs` LIKE '%singapore%' OR 
contacts.`exb_exhibitor_applicant` LIKE '%singapore%' ) 

        ORDER BY  fname
                    asc
        LIMIT 0, 50

需要 42 seoncds 来执行，这是很多时间。此处使用的所有 LIKE 字段都已创建索引。接下来将尝试使用临时表。

Answer 1

您正在使用 INNER JOIN，这表明即使在应用 WHERE 过滤器之前，只有在所有表中都有行的 id 才会带来数据。

我会使用 LEFT JOIN 看看。然后应用 WHERE 条件