title: Union as sub query MySQL
body: <p>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</p>
  
  <p>Here is the original query:</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>What I want to do is replace this  <code>( (Parts_Category.category_id = '508' OR Parts_Category.main_category_id ='508' )</code> 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.</p>
  
  <pre><code>(
      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
  )
  </code></pre>
  
  <p>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.</p>