MySQL Views - When to use & when not to

Question

the mysql certification guide suggests that views can be used for:

• creating a summary that may involve calculations
• selecting a set of rows with a WHERE clause, hide irrelevant information
• result of a join or union
• allow for changes made to base table via a view that preserve the schema of original table to accommodate other applications

but from how to implement search for 2 different table data?

And maybe you're right that it doesn't work since mysql views are not good friends with indexing. But still. Is there anything to search for in the shops table?

i learn that views dont work well with indexing so, will it be a big performance hit, for the convenience it may provide?

Solution 1

This mysql-forum-thread about indexing views gives a lot of insight into what mysql views actually are.

Some key points:

• A view is really nothing more than a stored select statement
• The data of a view is the data of tables referenced by the View.
• creating an index on a view will not work as of the current version
• If merge algorithm is used, then indexes of underlying tables will be used.
• The underlying indices are not visible, however. DESCRIBE on a view will show no indexed columns.

Solution 2

A view can be simply thought of as a SQL query stored permanently on the server. Whatever indices the query optimizes to will be used. In that sense, there is no difference between the SQL query or a view. It does not affect performance any more negatively than the actual SQL query. If anything, since it is stored on the server, and does not need to be evaluated at run time, it is actually faster.

It does afford you these additional advantages

• reusability
• a single source for optimization

Solution 3

MySQL views, according to the official MySQL documentation, are stored queries that when invoked produce a result set.

A database view is nothing but a virtual table or logical table (commonly consist of SELECT query with joins). Because a database view is similar to a database table, which consists of rows and columns, so you can query data against it.

Views should be used when:

• Simplifying complex queries (like IF ELSE and JOIN or working with triggers and such)
• Putting extra layer of security and limit or restrict data access (since views are merely virtual tables, can be set to be read-only to specific set of DB users and restrict INSERT )
• Backward compatibility and query reusability
• Working with computed columns. Computed columns should NOT be on DB tables, because the DB schema would be a bad design.

Views should not be use when:

• associate table(s) is/are tentative or subjected to frequent structure change.

Solution 4

One more down side of view that doesn't work well with mysql replicator as well as it is causing the master a bit behind of the slave.

http://bugs.mysql.com/bug.php?id=30998

Solution 5

According to http://www.mysqltutorial.org/introduction-sql-views.aspx

A database table should not have calculated columns however a database view should.

I tend to use a view when I need to calculate totals, counts etc.

Hope that help!

View more solutions