Federated Tables versus Views
Hello All, What should I consider when trying to choose between a Federated table and a View when I have one table's data that I'd like to be available in other databases on the same host. My view definition would be something like: CREATE [some options] VIEW [this_db].[this_view] AS SELECT [some_db].[some_table].* FROM [some_db].[some_table].* Are there performance gains to be had, one against the other? Security concerns? Replication gotchas? Thanks!
Re: Design advice
Hi Neil, On 10/5/2010 5:07 AM, Tompkins Neil wrote: Hi I have a number of tables of which I use to compute totals. For example I have table : players_master rec_id players_name teams_id rating I can easily compute totals for the field rating. However, at the end of a set period within my application, the values in the rating field are changed. As a result my computed totals would then be incorrect. Is the best way to overcome this problem to either compute the total and store as a total value (which wouldn't change in the future), or to store the rating values in a different table altogether and compute when required. If you need table information please let me know and I can send this. Many databases designed for rapid, time-based reporting do exactly as you propose: build a table just to hold the aggregate of a time-interval of values. Here's a rough example. Let's say that you run a web site and you want to track your traffic levels. Every second you may have thousands of hits, every hour hundreds of thousands of hits, and by the end of the week you may have hundreds of millions of individual data points to report on. To compute monthly stats, you are looking at a huge volume (billions) of data points unless you start aggregating. Lets say you build tables like: stats_hour, stats_day, stats_week, and stats_month. Every hour, you would take the last hour's worth of traffic and condense those values into the stats_hour table. At the end of the day, you take the previous 24 entries from stats_hour and compute a stats_day entry. Each level up aggregates the data from the level below. Does that give you an idea about how other people may have solved a similar problem? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Design advice
Hi Shawn Thanks for your response. In your experience do you think I should still retain the data used to generate the computed totals ? Or just compute the totals and disregard the data used ? Regards Neil On 8 Oct 2010, at 19:46, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: Hi Neil, On 10/5/2010 5:07 AM, Tompkins Neil wrote: Hi I have a number of tables of which I use to compute totals. For example I have table : players_master rec_id players_name teams_id rating I can easily compute totals for the field rating. However, at the end of a set period within my application, the values in the rating field are changed. As a result my computed totals would then be incorrect. Is the best way to overcome this problem to either compute the total and store as a total value (which wouldn't change in the future), or to store the rating values in a different table altogether and compute when required. If you need table information please let me know and I can send this. Many databases designed for rapid, time-based reporting do exactly as you propose: build a table just to hold the aggregate of a time- interval of values. Here's a rough example. Let's say that you run a web site and you want to track your traffic levels. Every second you may have thousands of hits, every hour hundreds of thousands of hits, and by the end of the week you may have hundreds of millions of individual data points to report on. To compute monthly stats, you are looking at a huge volume (billions) of data points unless you start aggregating. Lets say you build tables like: stats_hour, stats_day, stats_week, and stats_month. Every hour, you would take the last hour's worth of traffic and condense those values into the stats_hour table. At the end of the day, you take the previous 24 entries from stats_hour and compute a stats_day entry. Each level up aggregates the data from the level below. Does that give you an idea about how other people may have solved a similar problem? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Federated Tables versus Views
Neither, really. You can qualify a table with the database name to operate on tables in multiple databases on the same host. Federated is a way of accessing a table's data from a remote instance of mysql. -Original Message- From: winterb...@gmail.com [mailto:winterb...@gmail.com] On Behalf Of Wellington Fan Sent: Friday, October 08, 2010 9:52 AM To: mysql@lists.mysql.com Subject: Federated Tables versus Views Hello All, What should I consider when trying to choose between a Federated table and a View when I have one table's data that I'd like to be available in other databases on the same host. My view definition would be something like: CREATE [some options] VIEW [this_db].[this_view] AS SELECT [some_db].[some_table].* FROM [some_db].[some_table].* Are there performance gains to be had, one against the other? Security concerns? Replication gotchas? Thanks! This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
innodb backup
Hello, Is there any good document for backup of InnoDB? includes the increment backup and full backup. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
