DB: product_preview improvement

[ShuqingYe1997]

Currently our sql script to create product_preview is

CREATE  VIEW `product_preview` AS  
    SELECT   
        `product`.`pid` AS `pid`,  
        `product`.`uid` AS `uid`,  
        `product`.`title` AS `title`,  
        `product`.`cate_1` AS `cate_1`,  
        `product_status`.`end_time` AS `end_time`,  
        `product`.`auto_win_price` AS `auto_win_price`,  
        `product_status`.`price` AS `price`,  
        `product_status`.`status` AS `status`,  
        `figure`.`url` AS `url`  
    FROM  
        ((`product`  
        JOIN `product_status` ON ((`product`.`pid` = `product_status`.`pid`)))  
        JOIN `figure` ON ((`product`.`pid` = `figure`.`pid`)))  
    WHERE  
        (`figure`.`is_thumbnail` = 1)  

And the intermediate table (after 2 joins) can be considerably large. Please modify the sql to improve efficiency.

[ShuqingYe1997]

Now the sql is like:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `admin`@`%` 
    SQL SECURITY DEFINER
VIEW `product_preview` AS
    SELECT 
        `product`.`pid` AS `pid`,
        `product`.`uid` AS `uid`,
        `product`.`title` AS `title`,
        `product`.`cate_1` AS `cate_1`,
        `product_status`.`end_time` AS `end_time`,
        `product`.`auto_win_price` AS `auto_win_price`,
        `product_status`.`price` AS `price`,
        `product_status`.`status` AS `status`,
        `figure`.`url` AS `url`
    FROM
        ((`product`
        JOIN `product_status` ON ((`product`.`pid` = `product_status`.`pid`)))
        JOIN `figure` ON (((`product`.`pid` = `figure`.`pid`)
            AND (`figure`.`is_thumbnail` = 1))))

I move the figure.is_thumbnail = 1 from WHERE clause to JOIN clause, which rules out all non-thumbnail figure entries when doing join and reduce the intermediate table by a large margin.