0
select id 
from customer_details 
where store_client_id = 2 
  and id not in ( select customer_detail_id 
                  from  orders 
                  where store_client_id = 2 
                    and total_spent > 100 
                  group by customer_detail_id ) 
    or id in ( select tcd.id 
               from property_details as pd
                  , customer_details as tcd 
               where pd.store_client_id = 2 
                 and pd.customer_detail_id = tcd.customer_id 
                 and pd.property_key = 'Accepts Marketing' 
                 and pd.property_value = 'no' ) 
    and id IN ( select customer_detail_id 
                from orders 
                where store_client_id = 2 
                group by customer_detail_id 
                having count(customer_detail_id) > 0 ) 
    or id in ( select tor.customer_detail_id 
               from ordered_products as top
                  , orders as tor 
               where tor.id = top.order_id 
                 and tor.store_client_id = 2 
               group by tor.customer_detail_id 
               having sum(top.price) = 1 )`

I have this MySQL query with inner joins so when it runs in MySQL server it slows down. What is the issue I can't find?

But after 4-5 minutes it returns 15,000 records. These records is not an issue may be.

In some tutorial suggest to use Inner join, Left join,... But I don't know how to convert this query in Join clause. Any help will be appreciated. Thanks in advance.

closed as unclear what you're asking by Michael Green, Marco, MDCCL, Erik, Paul White Mar 18 '17 at 10:06

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    Please edit the question to include the definitions of the tables and indexes on them. How many rows are there in these tables? How many would you expect to match this query? – Michael Green Mar 17 '17 at 5:18
  • That's a complex query, please describe its purpose in words. – Rick James Mar 18 '17 at 1:25
  • OR, especially in this usage, cannot be optimized. IN ( SELECT ... ) is often un-optimizable. (Hence the reference recommending some form of JOIN.) – Rick James Mar 18 '17 at 1:27
  • If it should return 15K rows, I have to ask "what will you do with that many rows?" There may be another approach that gets the rows piecemeal that works better. – Rick James Mar 18 '17 at 1:28
2

I find it rather difficult to understand your query, so let's break it apart:

select id 
from customer_details 
where store_client_id = 2 
  and id not in ( select customer_detail_id 
                  from  orders 
                  where store_client_id = 2 
                    and total_spent > 100 
                  group by customer_detail_id ) 
    or id in ( select tcd.id 
               from property_details as pd
                  , customer_details as tcd 
               where pd.store_client_id = 2 
                 and pd.customer_detail_id = tcd.customer_id 
                 and pd.property_key = 'Accepts Marketing' 
                 and pd.property_value = 'no' ) 
    and id IN ( select customer_detail_id 
                from orders 
                where store_client_id = 2 
                group by customer_detail_id 
                having count(customer_detail_id) > 0 ) 
    or id in ( select tor.customer_detail_id 
               from ordered_products as top
                  , orders as tor 
               where tor.id = top.order_id 
                 and tor.store_client_id = 2 
               group by tor.customer_detail_id 
               having sum(top.price) = 1 )

This can be translated into:

select id 
from customer_details 
where store_client_id = 2 
  and id not in ( select customer_detail_id 
                  from  orders 
                  where store_client_id = 2 
                    and total_spent > 100 
                  group by customer_detail_id ) 

union

select id 
from customer_details 
where id in ( select tcd.id 
              from property_details as pd
                 , customer_details as tcd 
              where pd.store_client_id = 2 
                and pd.customer_detail_id = tcd.customer_id 
                and pd.property_key = 'Accepts Marketing' 
                and pd.property_value = 'no' ) 
  and id IN ( select customer_detail_id 
              from orders 
              where store_client_id = 2 
              group by customer_detail_id 
              having count(customer_detail_id) > 0 )

union

select id 
from customer_details 
where id in ( select tor.customer_detail_id 
              from ordered_products as top
                 , orders as tor 
              where tor.id = top.order_id 
                and tor.store_client_id = 2 
              group by tor.customer_detail_id 
              having sum(top.price) = 1 )

Now, the implicit joins should for readability be transformed into explicit ones:

select id 
from customer_details 
where store_client_id = 2 
  and id not in ( select customer_detail_id 
                  from  orders 
                  where store_client_id = 2 
                    and total_spent > 100 
                  group by customer_detail_id ) 

union

select id 
from customer_details 
where id in ( select tcd.id 
              from property_details as pd
              join  customer_details as tcd 
                on pd.customer_detail_id = tcd.customer_id 
              where pd.store_client_id = 2            
                and pd.property_key = 'Accepts Marketing' 
                and pd.property_value = 'no' ) 
  and id IN ( select customer_detail_id 
              from orders 
              where store_client_id = 2 
              group by customer_detail_id 
              having count(customer_detail_id) > 0 )

union

select id 
from customer_details 
where id in ( select tor.customer_detail_id 
              from ordered_products as top
              join orders as tor 
                  on tor.id = top.order_id 
              where tor.store_client_id = 2 
              group by tor.customer_detail_id 
              having sum(top.price) = 1 )

IN is often a slow predicate, I would try replacing it with [NOT] EXISTS. PITFALL: IN and EXISTS behaves different when nulls are involved, be careful so that you don't change the semantics of the query:

select id 
from customer_details as x 
where store_client_id = 2 
  and not exists (
         select 1 
         from  orders 
         where store_client_id = 2 
           and total_spent > 100
           and customer_detail_id = x.id ) 

union

[...]
0

The following is an untested attempt at distilling the problem into its basic components then unions the results together. The last query (with the correlated subquery) can be turned into a join if the introduction of orders does not cause a Cartesian product. I can not test with what is provided to see if this is a valid assumption.

        select customer_detail_id
          from orders 
         where store_client_id = 2
           and total_spent > 100
         group by customer_detail_id
 union  
        select tor.customer_detail_id
          from ordered_products as top,
               orders as tor 
         where tor.id = top.order_id
           and tor.store_client_id = 2 
         group by tor.customer_detail_id
        having sum(top.price) = 1
 union 
        select tcd.id
          from property_details as pd,
               customer_details as tcd 
         where pd.store_client_id = 2
           and pd.customer_detail_id = tcd.customer_id
           and pd.property_key = 'Accepts Marketing' 
           and pd.property_value = 'no') 
           and exists (select 1
                         from orders 
                        where customer_detail_id = tcd.id
                          and store_client_id = 2
                        group by customer_detail_id
                       having count(customer_detail_id) > 0 ) 

Not the answer you're looking for? Browse other questions tagged or ask your own question.