Is a view efficient in joins?

Hello:

I have a database with over 60 tables with thousands
to millions or rows in each.

I want to develop a summary of the data joined across
all the tables.

I can do this with a view, but I am concerned it will
take a lot of resources to perform all the joins required
by the view. Is a view efficient at making joins? Are
the joins executed every time the view is used or is
the data cached somehow?

The other approach is for me to create a table to hold
the summary data and write application code that periodically
updates it.

Which alternative would be best?

Thanks,
Neil

--
Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net
Host your MySQL driven app on a CentOS VPS for $25/mo
Unmetered bandwidth = no overage charges, 7 day free trial


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Is a view efficient in joins?

--0016e6d7e36392733104798bb075
Content-Type: text/plain; charset=ISO-8859-1

A view is no more or less efficient that the queries that make it up. Each
time you invoke the view, you repeat all the joins.

A join could be more efficient only if you go to a lot of effort to ensure
it forms the most efficient join(s) of the underlying tables.

Your solution of the summary table is actually a good one in many instances,
especially if it's not vital that it contain the most up-to-date data.

On Sun, Nov 29, 2009 at 6:16 PM, Neil Aggarwal wrote:

> Hello:
>
> I have a database with over 60 tables with thousands
> to millions or rows in each.
>
> I want to develop a summary of the data joined across
> all the tables.
>
> I can do this with a view, but I am concerned it will
> take a lot of resources to perform all the joins required
> by the view. Is a view efficient at making joins? Are
> the joins executed every time the view is used or is
> the data cached somehow?
>
> The other approach is for me to create a table to hold
> the summary data and write application code that periodically
> updates it.
>
> Which alternative would be best?
>
> Thanks,
> Neil
>
> --
> Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net
> Host your MySQL driven app on a CentOS VPS for $25/mo
> Unmetered bandwidth = no overage charges, 7 day free trial
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=jlyons4435@gmail.com
>
>


--
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com

--0016e6d7e36392733104798bb075--

RE: Is a view efficient in joins?

Jim:

> A view is no more or less efficient that the queries that
> make it up. Each
> time you invoke the view, you repeat all the joins.

That is what I was afraid of. With the large number
of tables I have, the joins are going to take a lot
of cycles to run.

> Your solution of the summary table is actually a good one in
> many instances,
> especially if it's not vital that it contain the most up-to-date data.

I think I will go that direction. It will be OK for
the summary data to be delayed by a short interval.

Thanks,
Neil

--
Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net
Host your MySQL driven app on a CentOS VPS for $25/mo
Unmetered bandwidth = no overage charges, 7 day free trial


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org