I want to save some analytics views to a permanent table periodically (hourly) in MySql-CodePudding

I created some views to analyze many tables so that the view has delay. But I need to run the faster query from the analytics views. So I am going to save the views to a table periodically. What is the good solution for this issue?

CodePudding user response：

I suppose this can be achieved with an EVENT. Make sure the global system variable EVENT_SCHEDULER is enabled.

set global event_scheduler=on;
delimiter //
drop event if exists hourly_job //
create event hourly_job on schedule every 1 hour starts now() do 
BEGIN
insert into your_analytic_table select * from your_view ;  -- put whatever you want to do in the body part
END//

The event_scheduler will execute the code in the body part based on the set interval, which in this case is set to every 1 hour. The time interval can of course be changed to meet your requirement. e.g every 10 minute