data base design issue - create view of last mounth on huge table or build it as a table

Question

I have a hits table with millions of records. I need to show some charts on the data of the last month.

Does the solution of create a view something like :

CREATE VIEW ReportMonth AS SELECT * FROM Report 
WHERE DayDate > DATE_SUB(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL 30
DAY)  

is good in terms of performance ,

Is there a better solution like cron that create this table every some hours ?

thanks

Answer 1

MySQL doesn't support materialized views but you can simulate it by just precreating the table yourself.

A suggestion of how to do this whilst minimising concurrency problems from here

CREATE TABLE new_materialized_view 
SELECT * from Report ...;

RENAME TABLE materialized_view TO old_materialized_view, 
new_materialized_view TO materialized_view;

DROP TABLE IF EXISTS old_materialized_view;

Hopefully someone else will tackle the scheduling options.

Answer 2

I do not think MySQL has such views but you need to copy to the new table. Also ensure that you have an index on the date field.

Answer 3

In MySQL the view is populated each time you make a query. So it does not give much performance improvement. You can opt for creating different tables (in case you don't have to update the data later or don't need to query them across tables/months) to improve the query performance.