MySQL Forums
Forum List  »  Newbie

Re: Daily view
Posted by: Phillip Ward
Date: October 07, 2014 05:52AM

Quote

I would like to create a view that spans over 24 hours with a specific start time.
OK. That's a good start.

Quote

Many users wants to access this data during a day so my intention is to reduce access time.
Using a View will not reduce access time. If anything, it may increase it.

Quote

The number of rows in the view can be up to 100 000.
Strictly speaking, Views do not contain rows.
They are nothing more than "Selects queries with Names".

Quote

Will the data be cached so that the view only will be filled with data on the first request made inside the date interval?
No, because Views don't contain any data and, therefore, can't cache anything.
What will happen is that all of these rows will be loaded into memory on first use (as with any query) so subsequent accesses should be faster (at least until those pages are swept out of memory again by other things with a more pressing need to be there).

Quote

Would creating a temporary table every day be a better alternative?
Possibly.
I might even suggest two tables with a view "on top" of them. Empty and fill the "inactive" table with a day's worth of data, then "toggle" the view to look at the data you've just loaded (which is fairly quick). The next day, repeat but with the other table.

Looking at the indexing on your table to improve your time-based queries would be a better option.

If you really need the horsepower, then consider partitioning your table.

Regards, Phill W.

Options: ReplyQuote


Subject
Written By
Posted
October 07, 2014 01:02AM
Re: Daily view
October 07, 2014 05:52AM
October 07, 2014 05:11PM
October 07, 2014 07:58PM
October 08, 2014 07:54AM
October 09, 2014 12:07AM
October 07, 2014 07:51PM


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.