MySql views performance

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.

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. Performance of VIEW vs. SQL statement
2. Is a view faster than a simple query?
3. Mysql VIEWS vs. PHP query
4. Are MySql Views Dynamic and Efficient?
5. Materialized View vs. Tables: What are the advantages?
6. 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