66

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:

  1. queries are much slower
  2. 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?

Community
  • 1
David LeBauer
  • 3,142
  • 8
  • 30
  • 34
  • It sounds like views are appropriate here, but I'm not sure what could be causing the slowdown when querying them. – FrustratedWithFormsDesigner Apr 11 '12 at 18:42
  • @FrustratedWithFormsDesigner are there any diagnostics that would help (short of creating a reproducible example)? The same complex query takes < 4s when done directly on joined tables and > 25s when done on views. Are views expected to not have a performance penalty? – David LeBauer Apr 11 '12 at 18:56
  • It's been a long time since I've used MySQL so I can't really say. – FrustratedWithFormsDesigner Apr 11 '12 at 19:00
  • I use MySQL and I will tell you views are terrible, unuseable when you get to 100K and above, just use straight queries where you have control over what fields to return and what joins to use – Stephen Senkomago Musoke Apr 11 '12 at 19:07

3 Answers3

51

Views in MySQL are handled using one of two different algorithms: MERGE or TEMPTABLE. 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 use MERGE because it is more efficient. Main Caveat:

If the MERGE algorithm cannot be used, a temporary table must be used instead. MERGE cannot be used if the view contains any of the following constructs:

  • Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)

  • DISTINCT

  • GROUP BY

  • HAVING

  • LIMIT

  • UNION or UNION ALL

  • Subquery in the select list

  • Refers only to literal values (in this case, there is no underlying table)

[src]

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:

For cases when materialization is required for a subquery in the FROM clause, the optimizer may speed up access to the result by adding an index to the materialized table. ... After adding the index, the optimizer can treat the materialized derived table the same as a usual table with an index, and it benefits similarly from the generated index. The overhead of index creation is negligible compared to the cost of query execution without the index.

As @ypercube points out, MariaDB 5.3 has added the same optimization. This article has an interesting overview of the process:

The optimization is applied then the derived table could not be merged into its parent SELECT which happens when the derived table doesn't meet criteria for mergeable VIEW

Community
  • 1
Derek Downey
  • 23,440
  • 11
  • 78
  • 104
17

Views are security tools. You do not want a particular user or application to know where your data table, you provide a view with only the columns it needs.

Remember that views always degrade performance, similar queries should be stored procedures and functions, not views.

To make a query tuning, always follow best practices, avoid using functions in WHERE clauses, create indexes to speed up selects, but do not abuse it indexes degrade inserts, updates and deletes.

There is good documentation that can assist you: http://www.toadworld.com/LinkClick.aspx?fileticket=3qbwCnzY/0A=&tabid=234

Rainier Morilla
  • 345
  • 1
  • 6
  • 7
    I disagree that views are (only) security tools. They can be used that way, but we use them to remove complexity in queries that our report developers use on a regular basis. – JHFB Apr 11 '12 at 19:07
  • 2
    @JHFB: I agree with you, but maybe that's only how it works in MySQL where it sounds like view incur serious performance penalties? – FrustratedWithFormsDesigner Apr 11 '12 at 19:23
  • @frustratedwithformsdesigner great point - it's been a while since I've used MySQL. – JHFB Apr 11 '12 at 19:24
  • 1
    @JHFB views on Mysql are a great problem! http://www.mysqlperformanceblog.com/2007/08/12/mysql-view-as-performance-troublemaker/ – Rainier Morilla Apr 11 '12 at 19:52
  • @RainierMorilla, Link is broken. – Pacerier Apr 30 '15 at 08:45
  • 2
    @RainierMorilla Views degrade performance!!?? – Suhail Gupta Jul 17 '16 at 16:50
  • Compare views in MySQL to Postgres materialized views, then you'll get an idea of how powerful a view can be on a platform that does it right. Views in MySQL are not done right, they seem to be more of kludge than anything else. – Lifeboy Feb 02 '19 at 10:19
-2

i think views are the predefined structure (no data) for merging tables into one to overcome from multiple table query, that can be used from real data for quick relational query's ...

Shahzad Shaikh
  • 1
  • 1
  • 2
    It's not very clear what point you are trying to make and how that addresses the issues laid out in the original post. You might want to re-read the question, but in any event please consider expanding your answer to make it clearer how it can be applied to the OP's problem. – Andriy M Nov 07 '18 at 10:13