How to Implement Materialized Views in MySQL / MS-SQL

Cliff Janson, Senior Java/DB ProgrammerJune 2013

A Materialized View (MV) replaces a SQL multi-table-view (or query) with a
new table that holds all data permutationsMV's are used to improve performance, and are preferable to
replication where problem is due to an inefficient query plan

Why views and complex queries can be slow:

An efficient query execution plan may not be viable

Joins/View criteria may not allow use of indexes

Joins/Views may involve calculations or string concatenations which can't be indexed

Using Joins/Views can force table-scans which have slow performance

Why deploy a MV?
Compared with a normal View, a properly designed/implemented MV can improve performance:

2x faster per table involved in the view (due to less I/O)

up to 10x faster for views that involve calculations

up to 100x faster for complex joins that force table scans

When to deploy a MV?
An MV logically should be deployed prior to:

replication for the purpose of improving performance (because it has a much smaller penalty)