Select query 2-3 times faster than view
- by Richard Knop
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?
