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?

Answer 1

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