Is it more efficient to query from a view in database than from table?

Suppose I have a table A, creating a view V from that table.

Then I do several queries from V. I wonder if V will be re-constructed each time I query? or it will be 开发者_Go百科constructed only 1 time, and being saved somewhere in memory by DBMS for next queries (which I think similar to query from a table)?

In general, no. V is a transient set of rows that is computed when requested by a query. Because you can apply additional WHERE and ORDER BY criteria when querying from a view, the execution plan for two queries against the same view could conceivably be quite different. The database generally cannot reuse the results of a previous query against a view to satisfy the next query against that view.

That said, there is a relatively new technology in some engines called Materialized Views. I have never used them myself, but my understanding is that these views are pre-computed based on updates that are made to the underlying tables. So with Materialize Views you do get improved SELECT performance, but at the expense of decrease INSERT, UPDATE, and DELETE performance.

You should also be aware that multi-column indexes can be used to precompute certain selections and sort orders involving individual tables. If you issue a query against a table that can be satisfied using a compound index (only the columns in the index are required by the query, and the sort order matches the index) then the table itself need never be read, only the index.

Views in MySQL are not a de facto caching solution.

MySQL runs the query against the base tables every time you query a view on those base tables. The results of the query are not stored for the view.

As a result, there is no need to "refresh" the view as there is when using materialized views in Oracle Microsoft SQL Server. Even the SQL in a MySQL view definition is re-evaluated every time you query the view.

If you need something like materialized views in MySQL, one tool that might help is FlexViews. This stores the results of a query in an ordinary base table, and then monitors changes recorded in MySQL's binary log, applying relevant changes to the base table. This tool can be quite useful, but it has some caveats:

• FlexViews is written in PHP, and as such it has some performance limitations. Depending on your write traffic load, FlexViews may not be able to keep up.
• It doesn't support every possible type of SELECT query.
• FlexViews-managed materialized view tables are not updateable. That is, you can UPDATE this view table, but the change will not apply to the base tables.

According to Pinal Dave, a view must be refreshed in order to reflect changes made to its referenced table(s). I'm not sure this makes a view of a simple 1-table query any more efficient than querying the table directly (it probably doesn't) but I think it means that views containing complex joins and subqueries may be more efficient than their non-view counterparts.

Pinal Dave has more to say about the other limitations of SQL views (or features, if you like). Maybe you can learn something useful there.

Mysql Views do not support Indexes. (as like in Oracle, where you can create index in Oracle Views) But mysql views can use the indexes in underlying table when created with Merge Algorithm.

If you have to use views, then adjust your JOIN BUFFER.

Using, Something like this

set global join_buffer_size=314572800;

Do profile the differences before and after changing the buffer size. I have seen after increasing join buffers, the view query executes in same time (in ms) as the table of the same size will do.