CODEWITHSUNDEEP
mysqlview
dodecafonico
dodecafonico

Reputation: 305

On the efficiency of using views of views in MySQL

I have recently been introduced to the concept of views and I am finding them a great help for splitting complex queries into parts.

My question is wether there are any efficiency disadvantages when I start making queries from views which they are in turn queries from other views, etc...

So I would for example have:

view1 -> query from tables A, B & C
view2 -> query from tables D, E & F
view3 -> query joining view1 & view2

Will there be any speed disadvantage when querying view3 instead of designing a single query that joins tables A, B, C, D, E & F?

And in case I would choose to use the views approach, does it matter, wether I have ORDER BY clauses in the design of view1, view2 & view3 or is it better that I don´t put any ORDER BY clause in any of the views and I just use ORDER BY when I query view3?

Thank you very much for your help! Boga.

Upvotes: 2

Views: 262

Answers (1)

Olaf Dietsche
Olaf Dietsche

Reputation: 74018

For order by see CREATE VIEW Syntax

ORDER BY is permitted in a view definition, but it is ignored if you select from a view using a statement that has its own ORDER BY.

And here on View Processing Algorithms, you can see how MySQL processes selects on views. As always, it depends. ;-)

It seems that the MERGE algorithm is the most efficient, because algorithm temptable copies the view results to a temporary table first and does the query on that. But you cannot always use merge, see the last section

If the MERGE algorithm cannot be used, a temporary table must be used instead. MERGE cannot be used if the view contains any of the following constructs:

  • Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
  • DISTINCT
  • GROUP BY
  • HAVING
  • LIMIT
  • UNION or UNION ALL
  • Subquery in the select list
  • Refers only to literal values (in this case, there is no underlying table)

Upvotes: 3

Related Questions