在大型数据集上提高子查询速度

Question

我有一个很大的数据集，我需要帮助来更快地进行一些查询。

到目前为止，我正在使用子查询来检索产品 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 秒。

任何使子查询更快的想法都得到了认可

谢谢

Answer 1

为什么不是内部连接？(假设product_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;

Answer 2

我会从切换到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列匹配。