------=_Part_65643_27139344.1136596390192
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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 faste=
r
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.

------=_Part_65643_27139344.1136596390192--

--=_alternative 00239022852570EF_=
Content-Type: text/plain; charset="US-ASCII"

Scott Klarenbach <doyouunderstand (AT) gmail (DOT) com> wrote on 01/06/2006 08:13:10
PM:


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/...imization.html

It's loaded with useful information.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


--=_alternative 00239022852570EF_=--

------=_Part_89316_7333871.1136834331139
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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 o=
f
thing, where the view internally compiles the where criteria from the
underlying table.

Scott Klarenbach

On 1/6/06, SGreen (AT) unimin (DOT) com <SGreen (AT) unimin (DOT) com> wrote:
------=_Part_89316_7333871.1136834331139--

SGreen (AT) unimin (DOT) com wrote:

had a hunch this was so. Is there any plan to include index support for
views / derived tables?

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak (AT) nusconsulting (DOT) com.au
website: http://www.nusconsulting.com.au

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw

Hi!

Daniel Kasak wrote:
An index is a separate data structure which must be maintained when the
base table is changed (in the indexed columns).

A view is a restricted (by rows and/or columns) look at a base table.

IMO, having separate indexes for views is not in line with the
relational approach at all.
The way to go is an efficient use of all indexes defined on a table,
whether it is accessed as a base table or via a view.
Any "where condition" in the view definition may be evaluated via base
table indexes, if suitable ones are defined on the base table - provided
the optimizer chooses this access path.

Improving the optimizer would be feature changes, so you should expect
to see that in newer versions only.

Regards,
Joerg

--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw