查询将为：

SELECT pother.id, count(*) as common
FROM product
JOIN category ON product.category = category.id
JOIN product pother ON pother.category = category.id
WHERE product.id = X
GROUP BY pother.id
ORDER BY common DEC LIMIT 10

如果product.category被索引，并且你把一个产品限制在10个类别，那么它可能不会太糟糕。
替代子查询表单

SELECT id, count(*) as common
FROM product
JOIN category
  ON product.category = category.id AND category.id IN (select category FROM product WHERE product.id = X)
GROUP BY id
ORDER BY comm DESC LIMIT 10

您可以将查询计划编辑到这个答案中吗？