mysqldatabaseperformanceoptimizationview

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?

Solution

  • 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. It's 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