联盟作为子查询MySQL

前端之家收集整理的这篇文章主要介绍了联盟作为子查询MySQL前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

我想使用union作为子查询来优化查询.我不确定如何构建查询.我正在使用MysqL 5

这是原始查询

  1. SELECT Parts.id
  2. FROM Parts_Category,Parts
  3. LEFT JOIN Image ON Parts.image_id = Image.id
  4. WHERE
  5. (
  6. (
  7. Parts_Category.category_id = '508' OR
  8. Parts_Category.main_category_id ='508'
  9. ) AND
  10. Parts.id = Parts_Category.Parts_id
  11. ) AND
  12. Parts.status = 'A'
  13. GROUP BY
  14. Parts.id

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

  1. (
  2. SELECT * FROM
  3. (
  4. (SELECT Parts_id FROM Parts_Category WHERE category_id = '508')
  5. UNION
  6. (SELECT Parts_id FROM Parts_Category WHERE main_category_id = '508')
  7. )
  8. as Parts_id
  9. )

谁能给我一个如何重写的线索?我已经尝试了几个小时但是无法得到它,因为我只是对MysqL很新.

最佳答案
  1. SELECT Parts.id
  2. FROM (
  3. SELECT parts_id
  4. FROM Parts_Category
  5. WHERE Parts_Category.category_id = '508'
  6. UNION
  7. SELECT parts_id
  8. FROM Parts_Category
  9. WHERE Parts_Category.main_category_id = '508'
  10. ) pc
  11. JOIN Parts
  12. ON parts.id = pc.parts_id
  13. AND Parts.status = 'A'
  14. LEFT JOIN
  15. Image
  16. ON image.id = parts.image_id

请注意,MysqL可以使用索引合并,您可以重写您的查询

  1. SELECT Parts.id
  2. FROM (
  3. SELECT DISTINCT parts_id
  4. FROM Parts_Category
  5. WHERE Parts_Category.category_id = '508'
  6. OR Parts_Category.main_category_id = '508'
  7. ) pc
  8. JOIN Parts
  9. ON parts.id = pc.parts_id
  10. AND Parts.status = 'A'
  11. LEFT JOIN
  12. Image
  13. ON image.id = parts.image_id

,如果您有以下索引,效率会更高:

  1. Parts_Category (category_id,parts_id)
  2. Parts_Category (main_category_id,parts_id)

猜你在找的MySQL相关文章