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

h43kikqp  于 2021-06-18  发布在  Mysql
关注(0)|答案(3)|浏览(249)

我有一个大的数据集(即每个表有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表中的不同行)。
有什么想法吗?或者是链接,我可以阅读更多关于这样的案例?

mccptt67

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 );
vfwfrxfs

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
smdnsysy

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、 我正在用手机打字,所以请原谅格式:)

相关问题