查询顾客与列出的产品一起购买了什么

Question

我正在尝试优化一个非常旧的查询，我无法理解它。我想要存档的结果是，我想向网上商店的访问者推荐其他客户感兴趣的内容，即他们与访问者正在查看的产品一起购买的其他内容。

我有一个子查询，但它非常慢，大约需要 15 秒处理大约 8 000 000 行。

布局是，放入用户购物篮中的所有产品都保存在表 wsBasket 中，并由 basketid 分隔（在另一个表中与成员关联）。

在这个例子中，我想列出用户与productid 427一起购买的所有最受欢迎的产品，但不列出productid 427本身。

SELECT productid, SUM(quantity) AS qty 
FROM wsBasket 
WHERE basketid IN 
    (SELECT basketid 
     FROM wsBasket 
     WHERE productid=427) AND productid!=427 
GROUP by productid 
ORDER BY qty 
DESC LIMIT 0,4;

非常感谢任何帮助！希望这至少对某人有意义:)

 

更新1： 感谢您的评论，伙计们，这是我的答案，它们不适合评论字段。

在上面的查询上使用 EXPLAIN 我得到了以下结果。请注意，我的表上没有任何索引（id 字段上的主键除外），我想修改查询以从索引中受益并将索引放置在正确的键上。

+----+--------------------+----------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type        | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+----+--------------------+----------+------+---------------+------+---------+------+------+----------------------------------------------+
|  1 | PRIMARY            | wsBasket | ALL  | NULL          | NULL | NULL    | NULL | 2821 | Using where; Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | wsBasket | ALL  | NULL          | NULL | NULL    | NULL | 2821 | Using where                                  |
+----+--------------------+----------+------+---------------+------+---------+------+------+----------------------------------------------+

Answer 1

需要添加两个明显的索引：一个在篮子 ID 上，第二个在产品 ID 上：然后重试查询和一个新的 EXPLAIN 以查看索引是否正在使用

Answer 2

除了确保 productid 和 basketid 上存在合适的索引之外，将查询构造为简单联接而不是子查询通常也会使您受益匪浅，尤其是在 MySQL 中。

SELECT b1.productid, SUM(b1.quantity) AS qty
FROM wsBasket AS b0
JOIN wsBasket AS b1 ON b1.basketid=b0.basketid
WHERE b0.productid=427 AND b1.productid<>427
GROUP BY b1.productid
ORDER BY qty DESC
LIMIT 4

对我来说，在可能相似的数据集上，连接在 EXPLAIN 输出中产生了两个 select_type: SIMPLE 行，而子查询方法则产生了可怕的性能问题依赖子查询。因此，连接速度快了一个数量级以上。

Answer 3

在此查询中主要用于搜索的两个字段是productid 和basketid。

当您搜索productid等于427的记录时，数据库不知道在哪里可以找到该记录。它甚至不知道如果确实找到了一个匹配项，则不会再有另一个匹配项，因此它必须查找整个表，可能有数千条记录。

索引是一个已排序的单独文件，并且仅包含您感兴趣的排序字段。因此创建索引可以节省大量时间！