Union as an additional MySQL query

Question

I want to optimize a query using join as a subquery. I'm really not sure how to build a query. I am using MYSQL 5

Here is the original request:

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 part ( (Parts_Category.category_id = '508' OR Parts_Category.main_category_id ='508' )with the union below. That way, I can refuse the GROUP BY clause and use direct col indexes, which should improve performance. Part and part category tables contain half a million entries, so any winnings will be big.

(
    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 someone let me know how to rewrite it? I have tried for several hours but cannot get it since I am only new to MySQL.

Answer 1

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, :

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

, :

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