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 http://stackoverflow.com/questions/1480953/how-to-implement-search-for-2-different-table-data/1481204#1481204

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?

Answer 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 statment
• 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.

Answer 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 differnce 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