Deploying Materialized Views

If you are a Web developer, sooner or later you will come across a complex SQL query that runs too slowly.  Web users typically won't wait over 2 seconds; they will choose to leave the site.  There are a limited number of ways to fix slow queries, which most people apply in this approximate order:

1. Add database indexes
2. Check the execution-plan to see if the query can be changed (or indexes added) to avoid table-scans
3. Consider striping the hard disk
4. Consider sharding the data (splitting it by key into multiple partitions)
5. Upgrade to faster storage (e.g. RAID or SAN or SSD's)
6. Replicate tables to split up high-demand-accesses, thus lowering the queue wait times

However, between #2 and #3 above, you should consider creating a Materialized View (MV).  An MV is a new table that contains the results of a View or Query, i.e. all combinations of the join's.  The MV will likely increase speeds 2x-5x, but also can be indexed for gains of 10x-50x.

Some databases, for example Oracle, include MV's.  SQL-Server has Indexed Views, which can help with performance, but are not quite as powerful.  MySQL does not currently have MV's.

The Web site www.comparehybridcars.net/MV gives a detailed example of how to create an MV in both MySQL and SQL-Server, including how to write triggers to automatically maintain it.