mysql - 写Mysql Query更高效

Question

如何使下面的 Mysql 查询更高效？

SELECT
   DISTINCT crm_task_id,
   table_header_id  
FROM
   table_details  
WHERE
   table_header_id  IN (
      SELECT
         table_header_id  
      FROM
         table_header  
      WHERE
         crm_campaign_id =196  
         AND crm_campaign_post_code_id  IN (
            SELECT
               crm_campaign_post_code_id  
            FROM
               crm_campaign_post_code  
            WHERE
               is_display_operator  IN (
                  1, 0 
               )  
         )  
         AND g_user_id  IN (
            SELECT
               g_user_id  
            FROM
               crm_user  
            WHERE
               is_active =1  
         )  
         AND DATE_FORMAT( created,  '%Y-%m-%d' )   BETWEEN  '2015-12-01'  AND  '2016-01-04'  
      )  
      AND crm_task_id NOT   IN (
         SELECT
            crm_task_id  
         FROM
            table_details  
         WHERE
            table_header_id  IN (
               SELECT
                  table_header_id  
               FROM
                  table_header  
               WHERE
                  crm_campaign_id =196  
                  AND crm_post_code_categ_id !=1000  
            )  
         )

table_header 列:

| table_header_id           | bigint(20)  
| created                   | datetime     
| updated                   | datetime    
| createdby                 | bigint(20)   
| updatedby                 | bigint(20)   
| is_active                 | char(1)     
| crm_user_session_id       | bigint(20)   
| crm_campaign_id           | bigint(20)   
| crm_post_code_categ_id    | bigint(20)  
| value                     | varchar(128) 
| crm_campaign_post_code_id | bigint(20)   
| crm_filter_id             | bigint(20)  
| g_user_id                 | bigint(20)  
| session_time              | int(100) 

table_details 列:

| table_details_id | bigint(11)  
| table_header_id         | bigint(11) 
| created                   | datetime    
| updated                   | datetime  
| createdby                 | bigint(11)
| updatedby                 | bigint(11) 
| is_active                 | smallint(5)
| crm_contact_id            | varchar(60)
| crm_task_id               | bigint(11)

上述查询需要超过 2 秒才能返回结果，请任何人重写此查询以更快地返回结果!!!

Answer 1

试试这个

 SELECT DISTINCT crm_task_id, td.table_header_id
    FROM table_details td
    WHERE table_header_id 
    JOIN table_header th
    ON td.table_header_id = th.table_header_id
    AND th.crm_campaign_id =196
    JOIN crm_campaign_post_code c
    ON c.crm_campaign_post_code_id = th.crm_campaign_post_code_id
    AND is_display_operator IN ( 1, 0 ) 
    JOIN crm_user u
    ON u.g_user_id = c.g_user_id
    AND is_active =1
    AND DATE_FORMAT( created,  '%Y-%m-%d' ) 
    BETWEEN  '2015-12-01'
    AND  '2016-01-04'
    WHERE u.crm_task_id  NOT 
    IN (
    SELECT IFNULL(crm_task_id,0)
    FROM table_details
    WHERE table_header_id
    IN (
    SELECT table_header_id
    FROM table_header
    WHERE crm_campaign_id =196
    AND crm_post_code_categ_id !=1000
   ))