Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySql views performance [closed]

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?

like image 229
Ed Heal Avatar asked Apr 24 '12 17:04

Ed Heal


People also ask

Do views improve performance MySQL?

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.

Do SQL views affect performance?

SQL Server views are helpful in many ways, for example in encapsulating complex multi-table query logic, allowing us to simplify client code. Views make queries faster to write, but they don't improve the underlying query performance.

Is view faster than query MySQL?

Contrary to the answers - In my experience, for views with lots of joins, doing a direct query runs faster.

Are views good for performance?

Yes, views can have a clustered index assigned and, when they do, they'll store temporary results that can speed up resulting queries. Microsoft's own documentation makes it very clear that Views can improve performance.


1 Answers

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
like image 163
Somnath Muluk Avatar answered Sep 29 '22 03:09

Somnath Muluk