How to optimize MySQL query with more than 2 inner joins
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 &gt; 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) &gt; 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 &gt; 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) &gt; 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 &gt; 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) &gt; 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 &gt; 100
           and customer_detail_id = x.id ) 

union

[...]