wiki:dbViewsInMySQL
Last modified 10 years ago Last modified on 11/17/2008 11:15:39 PM

Views in MySQL

LSST Database

Views (Non-materialized)

A view is a virtual table: it acts like a table but it contains no data. It is constructed using a SELECT statement which selects data from one or more tables. Changing the data in the underlying table(s) is immediately reflected in the view.

In MySQL, views were introduced in version 5.0. According to many sources, currently:

  • mysql optimizer is unable to optimize queries that use views
  • mysql views are almost always fully materialized per query.

In summary, one gets very poor performance with views.

Materialized Views

A materialized view is a pre-calculated and stored result of a query which selects data from one or more tables. Changing the data in underlying table(s) does not alter the data in the view, so in most cases refreshing a view is necessary. Such refresh can be done immediately after each update, or periodically. Immediate refresh tends to be expensive.

MySQL currently does not support materialized views. To overcome this, a commonly used solution involves creating a regular table using SELECT statement and relying on a combination of stored procedures and triggers to refresh such table. Some links explaining this: http://www.shinguz.ch/MySQL/mysql_mv.html, http://sourceforge.net/projects/flexviews/