mysql-按多个类别(多行…)更快地搜索产品？

h43kikqp 于 2021-06-18 发布在 Mysql

关注(0)|答案(3)|浏览(241)

我有一个大的数据集（即每个表有2000万行），看起来有点像这样（我不是真的在处理产品和目录，而是同样的情况）
餐桌产品

|id|name|created_at|
--------------------
|1 |....|2018-06...|
|2 |....|2018-06...|
|3 |....|2018-06...|
|4 |....|2018-06...|
|5 |....|2018-06...|
...etc.

还有一张分类表

|id|product_id|category|description|
------------------------------------
|1 |1         | abc    | def       |
|2 |1         | ghi    | jkl       |
|3 |1         | mno    | pqr       |
|4 |2         | abc    | stu       |
|5 |2         | wvx    | yz        |
...etc

我想做的是一个搜索，找到与许多类别的产品，即。

SELECT DISTINCTROW * FROM product WHERE

product.id in (
    SELECT categories.product_id FROM categories WHERE category = 'abc'
)
AND
product.id in(
    SELECT categories.product_id FROM categories WHERE category = 'ghi'
)
AND
product.id in(
    SELECT categories.product_id FROM categories WHERE category= 'mno'
)
AND 'some extra where' ORDER BY product.created_au LIMIT 10 offset 0

但这真的很慢。。。我尝试过不同的方法，但每个人都至少需要30秒。
我已经为用于连接的列建立了索引。
所以基本上我想做一个搜索，其中一个产品必须匹配一个或多个类别。另外，稍后，我还想进行搜索，以便产品匹配类别和描述（可能来自categories表中的不同行）。
有什么想法吗？或者是链接，我可以阅读更多关于这样的案例？

sql mysql inner-join

来源：https://stackoverflow.com/questions/52464285/mysql-search-for-a-product-by-many-categories-many-rows-faster

3条答案

按热度按时间

mccptt671#

第一个建议是可以在子查询上使用内部联接，而不是在子查询中使用内部联接

SELECT DISTINCTROW * 
    FROM product p 
    INNER JOIN  (
        SELECT categories.product_id FROM categories WHERE category = 'abc'
    ) t1 on p.id  = t1.product_id
    INNER JOIN (
        SELECT categories.product_id FROM categories WHERE category = 'ghi'
    ) t2 on p.id = t2.product_id
    INNER JOIN (
        SELECT categories.product_id FROM categories WHERE category= 'mno'
    ) t3 p.id = t3.product_id
    WHERE  'some extra where' 

    ORDER BY product.created_au LIMIT 10 offset 0

您还可以尝试使用单个子查询来获取3类别的所有产品标识

SELECT DISTINCTROW * 
    FROM product p 
    INNER JOIN  (
       SELECT categories.product_id FROM categories WHERE category IN ( 'abc','ghi', 'mno')
       group by categories.product_id 
       having count(distinct category ) = 3
    ) t1 on p.id  = t1.product_id
    WHERE  'some extra where' 

    ORDER BY product.created_au LIMIT 10 offset 0

如果内部连接没有如预期的那样加速，那么请确保在categories表上有适当的复合索引

CREATE INDEX index_name  ON categories(category,product_id );

赞(0）回复(0）举报 2021-06-18

vfwfrxfs2#

我只想用 GROUP BY 以及 HAVING 要获得产品：

select c.product_id
from categories c
where c.category in ('abc', 'ghi', 'mno')
group by c.product_id
having count(*) = 3;

你可以用 join , exists ，或 in 要获取其余的产品信息：

select p.*
from products p join
     (select c.product_id
      from categories c
      where c.category in ('abc', 'ghi', 'mno')
      group by c.product_id
      having count(*) = 3
     ) cp
     on c.product_id = p.i
where . . .  -- other conditions on product

赞(0）回复(0）举报 2021-06-18

smdnsysy3#

一张临时table应该有助于加快速度。

CREATE TEMPORARY TABLE tmpCategories( category_id INT)

找到将要搜索的类别的ID插入此表的方法。
然后写一个类似这样的连接：

SELECT p.* FROM product p
INNER JOIN categories c ON p.product_id = c.product_id
INNER JOIN tmpCategories tc ON tc.catrgory_id = c.category_id

您的查询可能与此不完全相同。但这种方法可能会加快速度。
p、我正在用手机打字，所以请原谅格式：）

赞(0）回复(0）举报 2021-06-18

我来回答

mysql-按多个类别(多行…)更快地搜索产品？

3条答案

相关问题

热门标签

最新问答