Scott Klarenbach <[EMAIL PROTECTED]> wrote on 01/06/2006 08:13:10 PM:
> Hello, > > I'm new to views and am discovering massive performance hits in the views > I've created once the records start to climb above 20,000 or so. Does > anyone know of a great primer/tutorial site for optimizing views in MySQL, > or even generally? What are the best practices etc...? I find when I > create the same view in SQL by joining the tables directly, it's much faster > than the views which invariably are joining other views. Is there a > recursion problem with this method? Should views only join underlying > tables and not other views? > > Thanks. > Scott. Treat views as you would any other query. All of the optimizations that normally apply to SELECT query performance should also apply to view performance. Views differ from tables in that they cannot be indexed. That is probably why you are getting performance hits by building views on views. Any query against a view (such as a second-tier derivative view) will end up performing the equivalent of a full table scan on any view it uses. There is no hard and fast rule about building views based on other views or based on tables. What works best for you should be which solution you stick with. If you have millions of rows in a base table and a view can reduce that to about ten thousand rows of summary information, I would be very tempted to stick with the view as the basis of a future query. You still have to generate that view each time you want to use it but its data may be sitting there in the query cache so it has the potential to be very fast. If I were you I would review the entire optimization chapter: http://dev.mysql.com/doc/refman/5.0/en/optimization.html It's loaded with useful information. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
