联合作为子查询MySQL [英] Union as sub query MySQL

问题描述

我想使用联合作为子查询来优化查询.我不是很确定如何构造查询.我正在使用MYSQL 5

I'm wanting to optimize a query using a union as a sub query. Im not really sure how to construct the query though. I'm using MYSQL 5

这是原始查询:

SELECT  Parts.id 
FROM    Parts_Category, Parts 
    LEFT JOIN Image ON Parts.image_id = Image.id 
WHERE 
( 
    (
        Parts_Category.category_id = '508' OR 
        Parts_Category.main_category_id ='508'
    ) AND 
    Parts.id = Parts_Category.Parts_id 
) AND 
Parts.status = 'A' 
GROUP BY 
    Parts.id 

我想做的是用下面的联合替换这个( (Parts_Category.category_id = '508' OR Parts_Category.main_category_id ='508' )部分.这样，我可以删除GROUP BY子句，并使用直接的col索引，这可以提高性能.零件和零件类别表各包含50万条记录，因此任何收益都会很大.

What I want to do is replace this ( (Parts_Category.category_id = '508' OR Parts_Category.main_category_id ='508' ) part with the union below. This way I can drop the GROUP BY clause and use straight col indexes which should improve performance. Parts and parts category tables contains half a million records each so any gain would be great.

(
    SELECT * FROM
    (
        (SELECT Parts_id FROM Parts_Category WHERE category_id = '508') 
        UNION 
        (SELECT Parts_id FROM Parts_Category WHERE main_category_id = '508')
    )
    as Parts_id
)

有人可以给我一个关于如何重写它的线索吗?我已经尝试了几个小时，但由于我只是MySQL的新手而无法获得它.

Can anybody give me a clue on how to re-write it? I've tried for hours but can't get it as I'm only fairly new to MySQL.

推荐答案

SELECT  Parts.id
FROM    (
        SELECT  parts_id
        FROM    Parts_Category
        WHERE   Parts_Category.category_id = '508'
        UNION
        SELECT  parts_id
        FROM    Parts_Category
        WHERE   Parts_Category.main_category_id = '508'
        ) pc
JOIN    Parts
ON      parts.id = pc.parts_id
        AND Parts.status = 'A'
LEFT JOIN
        Image
ON      image.id = parts.image_id

请注意，MySQL可以使用Index Merge，并且您可以这样重写查询:

Note that MySQL can use Index Merge and you can rewrite your query as this:

SELECT  Parts.id
FROM    (
        SELECT  DISTINCT parts_id
        FROM    Parts_Category
        WHERE   Parts_Category.category_id = '508'
                OR Parts_Category.main_category_id = '508'
        ) pc
JOIN    Parts
ON      parts.id = pc.parts_id
        AND Parts.status = 'A'
LEFT JOIN
        Image
ON      image.id = parts.image_id

，如果您具有以下索引，则效率会更高:

, which will be more efficient if you have the following indexes:

Parts_Category (category_id, parts_id)
Parts_Category (main_category_id, parts_id)

这篇关于联合作为子查询MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！