使子查询在大型数据集上更快

xt0899hw 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(245)

我有一个大的数据集，我需要帮助，使一些查询更快。
到目前为止，我使用子查询来检索产品ID，并获取该产品的不同人员（类似于过滤）
这是我的问题

SELECT assoc.*, count(assoc.product_id) as count FROM ws_products_persons_assoc as assoc 
        WHERE 
            assoc.product_id 
                IN 
                (   SELECT c.id 
                        FROM ws_products as c 
                    WHERE c.status = '1' 
                        AND ( ( product_name LIKE '%960%' ) OR ( ( code LIKE '%960%' OR isbn13 LIKE '%960%' OR parent_codes LIKE '%960%') ) OR ( publisher_name LIKE '%960%' ) OR ( author_name LIKE '%960%' ) ) 
                    ORDER BY c.year desc,c.product_name ASC 
                ) 

            GROUP BY assoc.person_id    
            ORDER BY count DESC LIMIT 0,30

Query Time =1.7937450408936 seconds

子查询在几个字段中搜索给定关键字
子查询返回183473行，运行时间为1.7秒。
任何使子查询更快的想法都是值得赞赏的
谢谢

sql mysql performance

来源：https://stackoverflow.com/questions/52606519/make-subquery-faster-on-large-dataset

2条答案

按热度按时间

yi0zb3m41#

为什么不是内部连接(假设产品id是唯一的）

SELECT a.*, count(a.product_id) as count
FROM ws_products_persons_assoc a INNER JOIN ws_products p
          ON p.id = a.product_id 
WHERE
    p.status = 1 AND
    ( (p.product_name LIKE '%960%' ) OR
    (p.code LIKE '%960%') OR
    (p.isbn13 LIKE '%960%' OR
    (p.parent_codes LIKE '%960%') OR
    (p.publisher_name LIKE '%960%') OR
    (p.author_name LIKE '%960%' )
    ) 

GROUP BY a.person_id    
ORDER BY count DESC
LIMIT 0, 30;

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

bqucvtff2#

我会先切换到 EXISTS :

SELECT a.person_id, count(a.product_id) as count
FROM ws_products_persons_assoc a 
WHERE EXISTS (SELECT 1
              FROM ws_products p
              WHERE p.id = a.product_id AND
                    p.status = 1 AND
                    ( (p.product_name LIKE '%960%' ) OR
                      (p.code LIKE '%960%') OR
                      (p.isbn13 LIKE '%960%' OR
                      (p.parent_codes LIKE '%960%') OR
                      (p.publisher_name LIKE '%960%') OR
                      (p.author_name LIKE '%960%' )
                   ) 
            ) 
GROUP BY a.person_id    
ORDER BY count DESC
LIMIT 0, 30;

对于这个版本，你需要一个索引 ws_products(id, status) .
这个 select 列应与 group by 柱。

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

我来回答

使子查询在大型数据集上更快

2条答案

相关问题

热门标签

最新问答