MySql views performance

If you are going down the road of using views, how can you ensure good performance?

Or is it better not to use views in the first place and just incorporate the equivalent into your select statements?

It Depends.

It totally depends on what you are viewing through view. But most probably reducing your effort and giving higher performance. When SQL statement references a nonindexed view, the parser and query optimizer analyze the source of both the SQL statement and the view and then resolve them into a single execution plan. There is not one plan for the SQL statement and a separate plan for the view.

A view is not compiled. Its a virtual table made up of other tables. When you create it, it doesn't reside somewhere on your server. The underlying queries that make up the view are subject to the same performance gains or dings of the query optimizer. I've never tested performance on a view VS its underlying query, but i would imagine the performance may vary slightly. You can get better performance on an indexed view if the data is relatively static. This may be what you are thinking maybe in terms of "compiled".

Advantages of views:

1. View the data without storing the data into the object.
2. Restrict the view of a table i.e. can hide some of columns in the tables.
3. Join two or more tables and show it as one object to user.
4. Restrict the access of a table so that nobody can insert the rows into the table.

See these useful links:

1. https://stackoverflow.com/questions/1867896/performance-of-view-vs-sql-statement
2. https://stackoverflow.com/questions/439056/is-a-view-faster-than-a-simple-query
3. https://stackoverflow.com/questions/4426919/mysql-views-vs-php-query
4. https://stackoverflow.com/questions/4886874/are-mysql-views-dynamic-and-efficient
5. https://stackoverflow.com/questions/4218657/materialized-view-vs-tables-what-are-the-advantages
6. https://stackoverflow.com/questions/4630633/is-querying-over-a-view-slower-than-executing-sql-directly
7. A workaround for the performance problems of TEMPTABLE views
8. See performance gains by using indexed views in SQL Server

Here's a tl;dr summary, you can find detailed evaluations from Peter Zaitsev and elsewhere.

Views in MySQL are generally a bad idea. At Grooveshark we consider them to be harmful and always avoid them. If you are careful you can make them work but at best they are a way to remember how to select data or keep you from having to retype complicated joins. At worst they can cause massive inefficiencies, hide complexity, cause accidental nested subselects (requiring temporary tables and leading to disk thrashing), etc.

It's best to just avoid them, and keep your queries in code.

I think the blog by Peter Zaitsev has most of the details. Speaking from personal experience views can perform well if you generally keep them simple. At one of my clients they kept on layering one view on top of another and it ended up in a perfomance nightmare.

Generally I use views to show a different aspect of a table. For example in my employees table show me the managers or hide the salary field from non HR employees. Also always make sure you run a EXPLAIN on the query and view to understand exactly what is happening inside MySQL.

If you want solid proof in your scenario I would suggest that you test. It is really hard to say using views is always a performance killer then again a badly written view is probably going to kill your performance.

They serve their purpose, but the hidden complexities and inefficiencies usually outweigh a more direct approach. I once encountered a SQL statement that was joining on two views, and sorting them the results. The views were sorting as well, so the execution time could be measured in what seemed like hours.

A thing not mentioned so far but making a huge difference is adequate indexing of the views' source tables.

As mentioned above, views do not reside in your DB but are rebuild every time. Thus everything that makes the rebuild easier for the DB increases performance of the view.

Often, views join data in a way that is very bad for storage (no normal form) but very good for further usage (doing analysis, presenting data to user, ...) and therewith joining and aggregating data from different tables.

Whether or not the columns on which the operations are made are indexed or not makes a huge difference on the performance of a view. If the tables and their relevant columns are indexed already accessing the view does not end in re-computing the indexes over and over again first. (on the downside, this is done when data is manipulated in the source tables)

! Index all columns used in JOINS and GROUP BY clauses in your CREATE VIEW statement !

If we are discussing "if you use views, how to ensure performance", and not the performance effect of views in general, I think that it boils down to restraint (as in yourself).

You can get in to big trouble if you just write views to make your query's simple in all cases, but do not take care that your views are actually usefull performance-wise. Any query's you're doing in the end should be running sane (see the comment example from that link by @eggyal). Ofcourse that's a tautology, but therefore not any less valuable

You especially need to be carefull not to make views from views, just because that might make it easier to make that view.

In the end you need to look at the reason you are using views. Any time you do this to make life easier on the programming end you might be better of with a stored procedure IMHO.

To keep things under control you might want to write down why you have a certain view, and decide why you are using it. For every 'new' use within your programming, recheck if you actually need the view, why you need it, and if this would still give you a sane execution-path. Keep on checking your uses to keep it speedy, and keep checking if you really need that view.