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.