Optimizing SQL query?

Question

I have a query in which I want to select all the customers who have orders with a particular product_id(say 12,13). I have made a fiddle for this. There is an orders table which contains the order_id and customer_id. Order_detail table has order_id and product_id. These are the sample tables. Original tables contain more than 30000 records. Could you please help me in optimising the queries or guide me the way to do so?

http://sqlfiddle.com/#!9/838da/3

Answer 1

You can use below final query-

SELECT customer_id,first_name 
FROM customer_detail cd 
JOIN orders ors ON cd.customer_id=ors.customer_id 
JOIN order_detail od ON od.order_id=ors.order_id 
WHERE product_id IN (12,13); 

Note: Below fields should be indexed - customer_id IN orders TABLE order_id AND product_id IN order_detail TABLE

Answer 2

select c.* from customer_detail c 
inner join orders o on o.customer_id = c.customer_id
inner join order_detail od on od.order_id = o.order_id
where od.product_id in (12,13);

I think this is what you are looking for.