CODEWITHSUNDEEP
mysqlsqlsql-view
Richard Knop
Richard Knop

Reputation: 83677

Select query 2-3 times faster than view

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?

Upvotes: 3

Views: 2559

Answers (1)

kevev22
kevev22

Reputation: 3775

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.

Upvotes: 6

Related Questions