mysql 语句查询 优化

Question

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 已做索引

请问如何优化以上查询语句？

Answer 1

用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

Answer 2

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.