Thanks a lot Shawn. I didn't realize that views don't take advantage of indexing. This is the cause of my major performance hits. I'm basically using views as a form of DB abstraction over the tables. So, many of my views pull all records from all tables they join, and it is up to the user to submit a where query to the view. In many cases, I'm getting 20-30 second queries, whereas the underlying (indexed) tables return results in .33 seconds.
The views themselves aren't using criteria. This runs contrary to what I imagine to be a common use of views, ie, vwSelectAllArizonaResidents sort of thing, where the view internally compiles the where criteria from the underlying table. Scott Klarenbach On 1/6/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > > 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 > >
