When creating tables from multiple joins for use in analysis, when is it preferred to use views versus creating a new table?
One reason that I would prefer to use views is that the database schema has been developed by our administrator from within Ruby, and I am not familiar with Ruby. I can request that tables be created, but requires an additional step and I would like more flexibility when developing / testing new joins.
I started using views following the answer to a related question on SO (When to use R, when to use SQL). The top-voted answer begins "do the data manipulations in SQL until the data is in a single table, and then do the rest in R."
I have started using views, but I have run into a few issues with views:
- queries are much slower
- Views do not get dumped from the production to backup database that I use for analysis.
Are views appropriate for this use? If so, should I expect a performance penalty? Is there a way to speed up queries on views?
Best Answer
Views in MySQL are handled using one of two different algorithms:
MERGE
orTEMPTABLE
.MERGE
is simply a query expansion with appropriate aliases.TEMPTABLE
is just what it sounds like, the view puts the results into a temporary table before running the WHERE clause, and there are no indexes on it.The 'third' option is
UNDEFINED
, which tells MySQL to select the appropriate algorithm. MySQL will attempt to useMERGE
because it is more efficient. Main Caveat:I would venture to guess your VIEWS are requiring the TEMPTABLE algorithm, causing performance issues.
Here is a really old blog post on the performance of views in MySQL and it doesn't seem to have gotten better.
There might, however, be some light at the end of the tunnel on this issue of temporary tables not containing indexes (causing full table scans). In 5.6:
As @ypercube points out, MariaDB 5.3 has added the same optimization. This article has an interesting overview of the process: