mysql - 无法为 3 表 INNER JOIN (MySQL) 找出有效的 SQL - "customers also bought"功能

Question

我正在尝试向我的网站添加典型的“购买了‘x’的客户也购买了‘y’”的功能。这是表结构:

Table: qb_invoice
+--------------------------------+------------------+------+-----+-------------------+----------------+
| Field                          | Type             | Null | Key | Default           | Extra          |
+--------------------------------+------------------+------+-----+-------------------+----------------+
| qbsql_id                       | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| TxnID                          | varchar(40)      | YES  | MUL | NULL              |                |
| Customer_ListID                | varchar(40)      | YES  | MUL | NULL              |                |
| Customer_FullName              | varchar(255)     | YES  |     | NULL              |                |
+--------------------------------+------------------+------+-----+-------------------+----------------+

Table: qb_invoice_invoiceline
+-------------------------+------------------+------+-----+-------------------+----------------+
| Field                   | Type             | Null | Key | Default           | Extra          |
+-------------------------+------------------+------+-----+-------------------+----------------+
| qbsql_id                | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| Invoice_TxnID           | varchar(40)      | YES  | MUL | NULL              |                |
| Item_ListID             | varchar(40)      | YES  | MUL | NULL              |                |
| Item_FullName           | varchar(255)     | YES  |     | NULL              |                |
+-------------------------+------------------+------+-----+-------------------+----------------+

Table: qb_customer
+-------------------------------------+------------------+------+-----+-------------------+----------------+
| Field                               | Type             | Null | Key | Default           | Extra          |
+-------------------------------------+------------------+------+-----+-------------------+----------------+
| qbsql_id                            | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| ListID                              | varchar(40)      | YES  | MUL | NULL              |                |
| Name                                | varchar(41)      | YES  | MUL | NULL              |                |
+-------------------------------------+------------------+------+-----+-------------------+----------------+

给定一个 Item_ListID 我想要一个快速、高效的查询来返回 Item_ListID 的列表以及订购每个项目的客户数量的 COUNT在列表中，所有客户都具有最初提供的 Item_ListID。

现在我有以下可用的 SQL，但速度很慢:

SELECT qb_invoice_invoiceline.Item_FullName, count(*) as 'nummy'
FROM qb_invoice_invoiceline
WHERE qb_invoice_invoiceline.Invoice_TxnID = 
ANY (SELECT qb_invoice.TxnID
FROM qb_invoice 
INNER JOIN qb_customer ON qb_invoice.Customer_ListID = qb_customer.ListID
INNER JOIN qb_invoice_invoiceline ON qb_invoice.TxnID = qb_invoice_invoiceline.Invoice_TxnID
WHERE qb_invoice_invoiceline.Item_ListID = '1360000-57')
GROUP BY qb_invoice_invoiceline.Item_ListID
ORDER BY nummy DESC

感谢您的帮助!

这是“解释”输出:

+----+--------------------+------------------------+-------+---------------------------+-------------+---------+-----------------------------------------+-------+----------------------------------------------+
| id | select_type        | table                  | type  | possible_keys             | key         | key_len | ref                                     | rows  | Extra                                        |
+----+--------------------+------------------------+-------+---------------------------+-------------+---------+-----------------------------------------+-------+----------------------------------------------+
|  1 | PRIMARY            | qb_invoice_invoiceline | index | NULL                      | Item_ListID | 123     | NULL                                    | 19690 | Using where; Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | qb_invoice_invoiceline | ref   | Invoice_TxnID,Item_ListID | Item_ListID | 123     | const                                   |     8 | Using where                                  |
|  2 | DEPENDENT SUBQUERY | qb_invoice             | ref   | Customer_ListID,TxnID     | TxnID       | 123     | func                                    |   206 | Using where                                  |
|  2 | DEPENDENT SUBQUERY | qb_customer            | ref   | ListID                    | ListID      | 123     | devdb.qb_invoice.Customer_ListID |    18 | Using where; Using index                     |
+----+--------------------+------------------------+-------+---------------------------+-------------+---------+-----------------------------------------+-------+----------------------------------------------+

Answer 1

如果您要加入的 varchar 字段上没有可用索引，您的查询可能会很慢。您能否详细说明这些表中存在的索引？

我认为查询将受益于 qb_invoice.TxnID 和 qb_customer.ListID 以及 qb_invoice_invoiceline.Item_ListID 上的索引。