In our project, we often end up writing complex joins over like 3 tables. Do we gain any performance advantages by using views or are they only for making the lives of query writers easier? In case it matters, we use MySQL. If any advantages are thus achieved (other than simpler queries of course) please illuminate.
+2
A:
Views are simply logical tables that arre no different than you running the same sql at runtime. The only difference would be a type of materialized view, like oracle uses, which is like a cached view.
REW
2010-07-05 04:19:18
I wanted to mark your answer along with jrista's as the correct answer but apparently only one answer could be marked as such, so I gave your answer an up vote.
ashy_32bit
2010-07-05 04:41:32
+4
A:
Generally speaking, normal views do not offer much of a performance improvement over just running a query. However, most database systems, including MySql (I believe...been a while since I used it) offer some kind of Indexed or Materialized view capability. Usually, such views have a fair amount of restrictions to be viable, but once created, the results of the backing query are cached in a physical table (in the case of SQL Server Indexed views, a table in TempDB.) The database server is then responsible for tracking changes to the underlying query, and updating the cached copy. Queries against such an indexed/materialized view are generally much faster, on the order of querying a normal table.
jrista
2010-07-05 04:23:04
A:
Views (normal views) do not offer performance advantages in themselves unless Indexed views (also know as a Materialized view) are used which have many restrictions. Not all views could be materialized.
ashy_32bit
2010-07-05 04:39:43