Select query 2-3 times faster than view
        Posted  
        
            by 
                Richard Knop
            
        on Stack Overflow
        
        See other posts from Stack Overflow
        
            or by Richard Knop
        
        
        
        Published on 2011-11-23T17:36:19Z
        Indexed on 
            2011/11/23
            17:51 UTC
        
        
        Read the original article
        Hit count: 576
        
This query run alone:
SELECT 
    -- lots of columns 
FROM   (((((((((((`table1` `t1`
                  LEFT JOIN `table2` `t2`
                    ON(( `t2`.`userid` = `t1`.`userid` )))
                 LEFT JOIN `table3` `t3`
                   ON(( `t1`.`orderid` = `t3`.`orderid` )))
                LEFT JOIN `table4` `t4`
                  ON(( `t4`.`orderitemlicenseid` =
                     `t3`.`orderitemlicenseid` )))
               LEFT JOIN `table5` `t5`
                 ON(( `t1`.`orderid` = `t5`.`orderid` )))
              LEFT JOIN `table6` `t6`
                ON(( `t5`.`transactionid` = `t6`.`transactionid` )))
             LEFT JOIN `table7` `t7`
               ON(( `t7`.`transactionid` = `t5`.`transactionid` )))
            LEFT JOIN `table8` `t8`
              ON(( `t8`.`voucherid` = `t7`.`voucherid` )))
           LEFT JOIN `table9` `t9`
             ON(( `t8`.`voucherid` = `t9`.`voucherid` )))
          LEFT JOIN `table10` `t10`
            ON(( ( `t10`.`vouchergroupid` = `t9`.`vouchergroupid` )
                 AND ( `t2`.`territoryid` = `t10`.`territoryid` ) )))
         LEFT JOIN `table11` `t11`
           ON(( `t11`.`voucherid` = `t8`.`voucherid` )))
        LEFT JOIN `table12` `t12`
          ON(( `t12`.`orderid` = `t1`.`orderid` )))
GROUP  BY `t5`.`transactionid`
Takes about 2.5 seconds to finish. When I save it to a view and run it as:
SELECT * FROM viewName;
It takes 7 seconds to finish. What is the reason and how can I make the view faster?
© Stack Overflow or respective owner