jx12345 jx12345 - 3 months ago 13
SQL Question

MySQL Get products also bought with a product / Optimise IN query

I'm trying to write a simple 'customers who bought this also bought...'

I have an

order
table, which contains orders, and an
order_product
table which contains all the products relating to an order.

In an attempt to find out the five most popular products that were bought with
product_id = 155
I've composed the following query:

select product_id, count(*) as cnt
from order_product
where product_id != 155
and order_id in
(select order_id from order_product where product_id = 155)
group by product_id
order by cnt desc
limit 5;


So the inner query gets a list of all the orders that have the product I'm interested in (product_id = 155) then the outer query looks for all the products that aren't the same product but are in the one of the order that my product is in.

They are then ordered and limited to the top 5.

I think this works ok but it takes ages - I imagine this is because I'm using IN with a list of a couple of thousand.

I wonder if anyone could point me in the direction of writing it in a more optimised way.

Any help much appreciated.

Answer

You could try changing this:

select p1.product_id, p1.count(*) as cnt

To

select p1.product_id, count(distinct p1.order_id) as cnt 

And see if that gives you any different result

Edit: From the comments

If you prefer having the result you generate in your first query, you can try using this:

select a.product_id, count(*) as cnt 
from order_product a
join (select distinct order_id from order_product where product_id = 155) b on (a.order_id = b.order_id)
where a.product_id != 155 
group by a.product_id 
order by cnt desc 
limit 5;

A small alteration to your existing query :)