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: 514

Filed under:
|
|

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

Related posts about mysql

Related posts about sql