MySQL Forums
Forum List  »  Stored Procedures

Re: consolidating temporal data
Posted by: Peter Brawley
Date: January 08, 2018 12:17AM

MySQL Views do not optimise well. You're adding something like half a million data rows a day, so yes, a regularly updated summary table will speed up report queries considerably.

Whether it's made with sprocs, functions or straightforward scriots doesn't matter much in terms of outcome. An sql script is probably easiest to develop, debug and maintain.

What you do need to get right, though, are the exact data needed in the table and the queries that will have to run against it, so I suggest the first thing is to write a first model of the summary table, populate it with realistic data, then write, test, optimise & debug the queries that will need to be run against it. In the course of doing that, you'll find yourself fine-tuning the table, and when you're done, you'll already have the required table structure and much of the script logic needed to update it--you'll just need to add date-specific scoping to the queries that update the summary table.

Options: ReplyQuote


Subject
Views
Written By
Posted
2518
January 07, 2018 10:38PM
Re: consolidating temporal data
907
January 08, 2018 12:17AM
507
January 09, 2018 01:55PM
498
January 09, 2018 05:27PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.