I am looking for information about views in MySQL such as performance
gains, what they can do, etc. I looked online and I couldn't exactly
find what I was looking for. Does anyone know of a good reference as
to where I can start?

What I am working on is an application that retrieves various XML
documents from different websites, parses them and then stores the
results in a database. I'm worried that as traffic to the site grows I
will constantly pulling information from the database (involving
numerous joins and a number of small, short queries) when the
information is relatively static. Would a view be the route to go? If
I were to generate a view every 20-30 minutes and then everytime there
is a request, I just query the database to return the view? Sorry if
what I'm saying does not make any sense, I have never used views
before. If there is another approach, fire away.

Thanks.

On Thu, 7 Feb 2008 10:33:47 -0800 (PST), tinman77 wrote:
> I am looking for information about views in MySQL such as performance
> gains, what they can do, etc. I looked online and I couldn't exactly
> find what I was looking for. Does anyone know of a good reference as
> to where I can start?

A handy starting point is here:

http://dev.mysql.com/tech-resources/...ysql-views.pdf

> What I am working on is an application that retrieves various XML
> documents from different websites, parses them and then stores the
> results in a database. I'm worried that as traffic to the site grows I
> will constantly pulling information from the database (involving
> numerous joins and a number of small, short queries) when the
> information is relatively static. Would a view be the route to go?

Maybe. Just making a bigger query that returns only the information
you're actually interested in *will* cut down on traffic. Sometimes
a view can make that kind of query run faster or easier to write.

> If
> I were to generate a view every 20-30 minutes and then everytime there
> is a request, I just query the database to return the view? Sorry if
> what I'm saying does not make any sense, I have never used views
> before. If there is another approach, fire away.

Generally, you should probably think of a view as kind of like a
prebuilt QUERY, not a prebuilt dataset. The dataset you're accessing
through a view is generated *when you access the view*, not before.
The interim "table" created by accessing the view may or may not stick
around in a cache, depending on how much activity your db server is
getting, how big the cache is, phase of the moon, whether the month name
has an R in it, etc.

--
64. I will see a competent psychiatrist and get cured of all extremely
unusual phobias and bizarre compulsive habits which could prove to
be a disadvantage.
--Peter Anspach's list of things to do as an Evil Overlord