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=arch...@jab.org
Re: Is a view efficient in joins?
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 n...@jammconsulting.comwrote: 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=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
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=arch...@jab.org
