Do I gain any performance advantages by using VIEWs rather than JOINs?

Question

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.

Answer 1

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.