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?
Basically, there are basically 2 types of views in MySQL.
Merge Views
This type of view basically just rewrites your queries with the view's SQL. It's a shorthand for writing the queries yourself. This offers no real performance benefit but makes writing complex queries easier, and, in turn, makes maintenance easier (since if the view definition changes, you don't need to change 100 queries against the view, only the one definition).
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 lets MySQL pick what it thinks is the best type at query time.
Something important to note - 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 for 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.