mysql query is not optimized

Question

I have tried to optimize this query but can't do it due to specific business requirements.

Tables:

• b2: about 2mil records, store business records
• business_reviews: small table, store reviews for businesses (each biz can have multiple reviews)
• business_feature_item: small table, store features for businesses (each biz can have multiple features

specific business requirements for the result:

• show business records
• show associated reviews for business as well
• allow to search for features too
• sort by b2.starbiz and the score (generated by MATCH AGAINST)

My current query use temp table, union all and sorting so it doesn't work very well when the result is large. Is there a way to re-write this query to make it work more efficient?

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

Using an OR clause will cause MySQL to not use any indexes, but instead do full table scans.

Try rewriting the query to use UNION ALLs instead of ORs:

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