order表
字段:order_id, product_id
查询 购买 产品A 的订单 同时会购买哪些 产品
SELECT prodcut_id, COUNT(*) AS nums
FROM order
LEFT JOIN (
SELECT order_id
FROM order
WHERE product_id = 100
) AS order_b ON order_b.order_id = order.order_id
WHERE order_b.order_id IS NOT NULL
GROUP BY product_id
ORDER BY nums DESC
oder_id, prodcut_id 已做索引
请问如何优化以上查询语句?
oder_id, prodcut_id 已做索引
索引是两索引分别在order_id, prodcut_id上? 还是一个索引在(order_id, prodcut_id)?
Query 1:
select product_id, count(product_id) from
order WHERE order_id in (Select order_id FROM order WHERE product_id = 100)
group by product_id;
Query 2:
select a.product_id, count(a.product_id) from
order a inner join order b on a.order_id = b.order_id and b.product_id = 100
group by product_id;
Query 2 is better.
用JOIN 替换掉LEFT JOIN:
SELECT prodcut_id, COUNT(*) AS nums
FROM order
JOIN (
SELECT order_id
FROM order
WHERE product_id = 100
) AS order_b ON order_b.order_id = order.order_id
GROUP BY product_id
ORDER BY nums DESC