MySql views performance [closed]

by

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

More Related Contents:

  1. MyISAM versus InnoDB [closed]
  2. MYSQL OR vs IN performance
  3. Storing JSON in database vs. having a new column for each key
  4. MySQL stored procedures use them or not to use them
  5. View’s SELECT contains a subquery in the FROM clause
  6. DOs and DONTs for Indexes [closed]
  7. “SELECT COUNT(*)” is slow, even with where clause
  8. Should I normalize my DB or not?
  9. How to speed up sql queries ? Indexes?
  10. How can i optimize MySQL’s ORDER BY RAND() function?
  11. ‘IF’ in ‘SELECT’ statement – choose output value based on column values
  12. 1052: Column ‘id’ in field list is ambiguous
  13. Why does MYSQL higher LIMIT offset slow the query down?
  14. How to do a batch insert in MySQL
  15. Count the occurrences of DISTINCT values
  16. Is there a REAL performance difference between INT and VARCHAR primary keys?
  17. Codeigniter – multiple database connections
  18. JOIN queries vs multiple queries
  19. Is the primary key automatically indexed in MySQL?
  20. Storing Data in MySQL as JSON
  21. MySql: MyISAM vs. Inno DB! [closed]
  22. MySQL: Cloning a MySQL database on the same MySql instance
  23. How to change the default port of mysql from 3306 to 3360
  24. Meaning of “n:m” and “1:n” in database design
  25. How to set a MySQL row to READ-ONLY?
  26. Case Sensitive collation in MySQL
  27. Can’t import database through phpmyadmin file size too large
  28. is php sort better than mysql “order by”?
  29. How can “set timestamp” be a slow query?
  30. #1130 – Host ‘localhost’ is not allowed to connect to this MySQL server

Leave a Comment