mysql left join复杂一点的查询方法示例

SELECT a.id,b.series_id,COUNT(b.id) cnt FROM 
(SELECT id FROM lib_brand_series WHERE 2>1 AND brand_id=1 AND category_id=5 AND STATUS=1) a 
LEFT JOIN 
(SELECT id,series_id FROM lib_product_index a WHERE 2>1 AND small_category_id = 252 AND brand_id = 1) b 
ON a.id = b.series_id GROUP BY a.id

执行计划：
[img]http://dl.iteye.com/upload/attachment/585523/f48e5649-cc87-32e6-a2ff-bcc6a21fd14e.jpg[/img]


优化后sql有一点点修改：

SELECT a.id,COUNT(b.id)
FROM lib_brand_series a LEFT JOIN 
(SELECT * FROM lib_product_index WHERE small_category_id = 252 AND brand_id = 1)b
ON a.id = b.series_id
WHERE a.brand_id=1 
    AND a.category_id=5 
    AND a.STATUS=1 
GROUP BY a.id

执行计划：
[img]http://dl.iteye.com/upload/attachment/585525/f1b32248-e051-31a8-a922-a746b692aa2c.jpg[/img]

如图所示：

[img]http://dl.iteye.com/upload/attachment/584469/fafb7931-2ee2-3a7a-8d2e-1e42a4a4cfcc.jpg[/img]