mysql 查询优化经验

直接join原表查询比join一张虚拟表查询的性能要好很多。

代码1（查询耗时206毫秒）：

select p.pro_id, p.child_brand_name, p.subject, p.main_photo, MIN(c.price2) minPrice, MAX(c.price2) maxPrice 
            from www_car c
            JOIN www_product p on p.pro_id = c.pro_id
            JOIN www_brand b on b.brand_id = p.brand_id
            LEFT JOIN www_car_price cp on cp.car_id = c.car_id
            WHERE cp.city_id=6 AND b.brand_id =54
            GROUP BY p.pro_id 

代码2（查询耗时466毫秒）：

select p.pro_id, p.child_brand_name, p.subject, p.main_photo,

            t.minPrice, t.maxPrice
            from www_product p JOIN (
                    SELECT c.pro_id, MIN(c.price2) minPrice, MAX(c.price2) maxPrice FROM www_car c where c.brand_id=54 GROUP BY c.pro_id
            ) t ON t.pro_id = p.pro_id
            JOIN www_car c on c.pro_id = p.pro_id
            JOIN www_car_price cp on cp.car_id = c.car_id
            WHERE cp.city_id=6 AND p.brand_id =54
            GROUP BY p.pro_id