Are MySql Views Dynamic and Efficient?

Question

I'm looking to create a view of a table which will highlight data that meets a specific criteria. For example, if I have a table with integer values, I want my view to show the rows which have a value greater than 100. I know how to achieve this by creating a view on a table, however is the view dynamic? I have tested this in MySQL and it seems to be true. But if my table has over 1000 rows, is this efficient? Will the view still update "dynamically" to any changes in the original table?

Answer 1

Basically, there are basically 2 types of views in MySQL.

1. Merge Views

   This type of view basically just re-writes your queries with the view's SQL. So it's a short-hand for writing the queries yourself. This offers no real performance benefit, but make writing complex queries easier and making maintenance easier (since if the view definition changes, you don't need to change 100 queries against the view, only the one definition).

2. Temptable Views

   This type of view creates a temporary table with the query from the view's SQL. It has all the benefits of the merge view, but also reduces lock time on the view's tables. Therefore on highly loaded servers it could have a fairly significant performance gain.

There's also the "Undefined" view type (the default), which let's MySQL pick what it thinks is the best type at query time...

But note something important to note, is that MySQL does not have any support for materialized views. So it's not like Oracle where a complex view will increase the performance of queries against it significantly. The queries of the views are always executed in MySQL.

As far as the efficiency, Views in MySQL do not increase or decrease efficiency. They are there to make your life easier when writing and maintaining queries. I have used views on tables with hundreds of millions of rows, and they have worked just fine...