Select query 2-3 times faster than view

Question

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 t10.[territoryid] = t2.[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?

Why do you repeatedly double-open and double-close your parenthesis? — Matthew, Nov 23 '11 at 17:39
@Matthew PK That's not me, I just used SQL formatting tool before I posted the SQL here. There are no parenthesis in the actual SQL. — Richard Knop, Nov 23 '11 at 17:41
@Matthew PK No, no where clause at the moment (I want to figure why it's so slower in the view before adding where). — Richard Knop, Nov 23 '11 at 17:42
http://www.mysqlperformanceblog.com/2007/08/12/mysql-view-as-performance-troublemaker/ — ajreal, Nov 23 '11 at 17:46
possible duplicate of [mysql view performance](http://stackoverflow.com/questions/2760475/mysql-view-performance) — Andrew Marshall, Nov 23 '11 at 17:48

score 6 · Accepted Answer · edited May 23 '17 at 12:30

There are performance problems with the way views are handled in MySql. Check out this answer here.

Basically there are 2 different algorithms that are used for views in MySql: Merge and Temptable. Merge usually performs quite well but Temptable can perform quite poorly. Merge cannot be used when the view contains certain constructs including GROUP BY, so your view would be using the Temptable algorithm.

Select query 2-3 times faster than view

1 Answers1