联合作为子查询 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1159384/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Union as sub query MySQL
提问by gus
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
我想使用联合作为子查询来优化查询。我不太确定如何构造查询。我正在使用 MYSQL 5
Here is the original query:
这是原始查询：
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
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.
我想做的是用( (Parts_Category.category_id = '508' OR Parts_Category.main_category_id ='508' )下面的联合替换这 部分。这样我就可以删除 GROUP BY 子句并使用应该提高性能的直列索引。零件和零件类别表各包含 50 万条记录，因此任何收益都会很大。
(
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
)
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.
任何人都可以给我一个关于如何重写它的线索吗？我已经尝试了几个小时但无法获得它，因为我对 MySQL 还很陌生。
采纳答案by Quassnoi
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
Note that MySQLcan use Index Mergeand you can rewrite your query as this:
请注意，MySQL可以使用Index Merge并且您可以将查询重写为：
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)
