1

How can I make this below Mysql query more efficient ?

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 columns :

| 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 Columns:

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

the above query takes more than 2 seconds to return result, Please can anyone rewrite this query to return result faster !!!

Improve this question
4
  • Can you see if it might be possible to use joins instead of subqueries inside the WHERE clause? Commented Jan 4, 2016 at 5:48
  • okay I will try and let you know Commented Jan 4, 2016 at 5:54
  • @TimBiegeleisen its slow when using join, Header table contains 1,94,323 and details table contains 8,70,842 records Commented Jan 4, 2016 at 8:47
  • A large number of records is precisely the situation where one would want to use joins rather than subqueries as a means of restricting records at some point during the execution of a query. Commented Jan 4, 2016 at 8:49

4 Answers 4

Reset to default
1

try this

 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
   ))
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

it takes 2.45 seconds
Header table contains 1,94,323 and details table contains 8,70,842 records
do you use indexes in tables ?
Yes , table_details contains index for column "crm_task_id" , table_header table contains indexes on g_user_id, crm_filter_id and group index on crm_campaign_id,crm_post_code_categ_id,crm_campaign_post_code_id
one more thing can you use subquery with join may be it will help you.like JOIN (select table_header_id,...... from table_header where crm_campaign_id =196) as th ON td.table_header_id = th.table_header_id
|
1

Try this one.

SELECT
   DISTINCT td.crm_task_id,
   td.table_header_id      
FROM
   table_details td      
join
   table_header th 
      on th.table_header_id = td.table_header_id 
      and th.crm_campaign_id =196 
      and th.crm_post_code_categ_id !=1000      
join
   crm_campaign_post_code ccpc 
      on ccpc.crm_compaign_post_code_id =  th.crm_campaign_post_code_id 
      and ccpc.is_display_operator IN (
         1,
      0 ) 
      and DATE_FORMAT( created,
      '%Y-%m-%d' ) BETWEEN  '2015-12-01' AND  '2016-01-04'      
   join
      crm_user cr 
         on cr.g_user_id = ccpc.g_user_id 
         and cr.is_active = 1
Improve this answer

4 Comments

I think the query mentioned by @Naveed is a optimized one. Just one clarification I need is on first JOIN condition i.e. on th.table_header_id = td.table_header_id and th.crm_campaign_id =196 and th.crm_post_code_categ_id !=1000 should it be replace with on th.table_header_id = td.table_header_id and th.crm_campaign_id !=196 and th.crm_post_code_categ_id =1000
@MohitAggarwal thanks for your feedback and correction.
Execution times increased when we use IN or nested query so instead of that, we can use joins.
Ah ok. Actually I have a web portal www.everything.pk which have cities, brands, stores with respect to brands and cities and areas and I was using same as yours query (like IN or nested query) and page load or execution was too slow up to 3,4 seconds. while after some research, I updated query to JOINS and query execution time was .03, .05 like this. so that's why I was sure that your issue will also be resolved using JOINS.
0

Try this one :

    SELECT DISTINCT crm_task_id, table_header_id
FROM table_details
WHERE table_header_id
IN (
    select table_header_id From 
    (
    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 (
        SELECT crm_campaign_post_code_id
        FROM crm_campaign_post_code
        WHERE is_display_operator
        IN ( 1, 0 )
        ) temp2
    )
    AND g_user_id
    IN (
        select g_user_id from (
        SELECT g_user_id
        FROM crm_user
        WHERE is_active =1
        ) temp3
    )
    AND DATE_FORMAT( created,  '%Y-%m-%d' ) BETWEEN  '2015-12-01' AND  '2016-01-04'
    ) as temp_1
)
AND crm_task_id NOT IN (
    SELECT crm_task_id FROM table_details WHERE table_header_id
    IN (
        select table_header_id From (
        SELECT table_header_id
        FROM table_header
        WHERE crm_campaign_id =196
        AND crm_post_code_categ_id !=1000
        ) temp4
    )
)
Improve this answer

1 Comment

above query takes more than 3 secs
0

Avoid IN ( SELECT ... ); it does not optimize well. Instead turn it into a JOIN. For example:

SELECT  crm_task_id
    FROM  table_details
    WHERE  table_header_id IN (
        SELECT  table_header_id
            From  
              ( SELECT  table_header_id
                    FROM  table_header
                    WHERE  crm_campaign_id =196
                      AND  crm_post_code_categ_id !=1000 ) temp4 )

-->

SELECT  crm_task_id
    FROM  table_details AS d
    JOIN  table_header AS h ON d.table_header_id = h.table_header_id
    WHERE  h.crm_campaign_id =196
      AND  h.crm_post_code_categ_id !=1000
Improve this answer

Comments

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.