Mysql should i use views

Ask Question. Asked 9 years, 7 months ago. Active 3 years ago. Viewed 88k times. Improve this question. Community Bot 1. It sounds like views are appropriate here, but I'm not sure what could be causing the slowdown when querying them. FrustratedWithFormsDesigner are there any diagnostics that would help short of creating a reproducible example? Are views expected to not have a performance penalty? I use MySQL and I will tell you views are terrible, unuseable when you get to K and above, just use straight queries where you have control over what fields to return and what joins to use — Stephen Senkomago Musoke.

Add a comment. Active Oldest Votes. This article has an interesting overview of the process: The optimization is applied then the derived table could not be merged into its parent SELECT which happens when the derived table doesn't meet criteria for mergeable VIEW.

Improve this answer. Derek Downey Derek Downey I have done no testing on these claims but MariaDB 5. Rainier Morilla Rainier Morilla 1 1 silver badge 6 6 bronze badges. I disagree that views are only security tools.

They can be used that way, but we use them to remove complexity in queries that our report developers use on a regular basis. JHFB views on Mysql are a great problem!

RainierMorilla Views degrade performance!!?? Show 2 more comments. Shahzad Shaikh Shahzad Shaikh 1 1 1 bronze badge. In addition, MySQL even allows you to create a view that does not refer to any table. But you will rarely find this kind of view in practice. For example, you can create a view called daysofweek that return 7 days of a week by executing the following query:. Views help simplify complex queries.

If you have any frequently used complex query, you can create a view based on it so that you can reference to the view by using a simple SELECT statement instead of typing the query all over again.

Suppose you have to repeatedly write the same formula in every query. Or you have a query that has complex business logic. To make this logic consistent across queries, you can use a view to store the calculation and hide the complexity.

A table may expose a lot of data including sensitive data such as personal and banking information. View definitions fail to replicate to newer replicas that enforce the column-length restriction. Dump files created with mysqldump cannot be loaded into servers that enforce the column-length restriction. A workaround for either problem is to modify each problematic view definition to use aliases that provide shorter column names.

Then the view replicates properly, and can be dumped and reloaded without causing an error. However, this does not change the original view definitions, which may cause problems for subsequent dump operations. Restrictions on Stored Programs. Restrictions on Views. Restrictions on Condition Handling. Restrictions on Server-Side Cursors.

Restrictions on Subqueries. Restrictions on XA Transactions. Restrictions on Character Sets. Restrictions on Performance Schema. Restrictions on Pluggable Authentication. Restrictions and Limitations on Partitioning. Partitioning Limitations Relating to Storage Engines.