mysql查询没有优化

Question

我已经尝试过优化这个查询，但是由于特定的业务需求，我做不到。

表：

• b2：大约200万条记录，存储业务记录
• business_reviews：小型表，针对企业的商店评论(每个biz可以有多个评论)
• business_feature_item：用于企业的小型表、存储功能(每个biz可以有多个功能)

对结果的具体业务要求：

• 显示业务记录
• 显示与业务相关的评论。
• 也允许搜索功能
• 按b2.starbiz和分数排序(由匹配生成)

我当前的查询使用临时表、union和排序，这样当结果很大时，它就不能很好地工作。有没有一种方法可以重写这个查询，使其更有效？

SELECT temp.* FROM 

(SELECT DISTINCT b.business_id, b.description AS `extra`, '1' AS `type`, 0 as score 

FROM b2 as b 

LEFT JOIN business_feature_item AS i ON b.business_id = i.business_id 

WHERE ((b.cat_id = '93' OR b.cat_id2 = '93' OR b.cat_id3 = '93')) 

AND b.city_id = '152262' 

AND `approved`=1 

UNION ALL SELECT b.business_id, review_desc AS `extra`, '2' AS `type`, ((MATCH         `review_desc` AGAINST ('"restaurants"' IN BOOLEAN MODE) * 4) + (MATCH `review_desc` AGAINST ('restaurants' IN BOOLEAN MODE) )) AS score 

FROM b2 AS b, business_reviews AS r 

WHERE b.business_id =r.business_id 

AND b.city_id = '152262' 

AND ( MATCH `review_desc` AGAINST ('"restaurants"' IN BOOLEAN MODE) 

OR MATCH `review_desc` AGAINST ('restaurants' IN BOOLEAN MODE))) 

AS temp 

GROUP BY temp.business_id 

ORDER BY starbiz DESC, score DESC 

Answer 1

使用OR子句将导致MySQL不使用任何索引，而是执行完整的表扫描。

尝试重写查询以使用UNION ALL而不是OR：

SELECT temp.* FROM 
(
    SELECT DISTINCT 
        b.business_id, 
        b.description AS `extra`,
        '1' AS `type`,
        0 as score 
    FROM 
        b2 as b 
    LEFT JOIN 
        business_feature_item AS i ON b.business_id = i.business_id 
    WHERE 
        b.cat_id = '93'
        AND b.city_id = '152262' 
        AND `approved`=1 
UNION ALL 
    SELECT DISTINCT 
        b.business_id, 
        b.description AS `extra`,
        '1' AS `type`,
        0 as score 
    FROM 
        b2 as b 
    LEFT JOIN 
        business_feature_item AS i ON b.business_id = i.business_id 
    WHERE 
        b.cat_id2 = '93'
        AND b.city_id = '152262' 
        AND `approved`=1 
UNION ALL 
    SELECT DISTINCT 
        b.business_id, 
        b.description AS `extra`,
        '1' AS `type`,
        0 as score 
    FROM 
        b2 as b 
    LEFT JOIN 
        business_feature_item AS i ON b.business_id = i.business_id 
    WHERE 
        b.cat_id3 = '93'
        AND b.city_id = '152262' 
        AND `approved`=1 
UNION ALL 
    SELECT 
        b.business_id,
        review_desc AS `extra`,
        '2' AS `type`,
        MATCH `review_desc` AGAINST ('"restaurants"' IN BOOLEAN MODE) * 4 AS score
    FROM 
        b2 AS b,
        business_reviews AS r 
    WHERE 
        b.business_id =r.business_id 
        AND b.city_id = '152262' 
        AND MATCH `review_desc` AGAINST ('"restaurants"' IN BOOLEAN MODE) 
UNION ALL 
    SELECT 
        b.business_id,
        review_desc AS `extra`,
        '2' AS `type`,
        MATCH `review_desc` AGAINST ('restaurants' IN BOOLEAN MODE) AS score 
    FROM 
        b2 AS b,
        business_reviews AS r 
    WHERE 
        b.business_id =r.business_id 
        AND b.city_id = '152262' 
        AND MATCH `review_desc` AGAINST ('restaurants' IN BOOLEAN MODE)
) 
AS temp 
GROUP BY temp.business_id 
ORDER BY starbiz DESC, score DESC