Very big tables take too long to load

A know XtreamUI issue is that over time the XtreamUI database will be flooded with logs which leads to big tables take too long to load.

Crontab Version

To open crontab with text editor enter following command

crontab -e

Once you enter above command you will be asked to choose a text editor. Choose nano if you are first time editing or go with your choice. After choosing text editor following window will open.
Set up crontab in Ubuntun
Set up crontab in Ubuntun
Add following entries, this clear every day on 8am the logs from the Database

0 8 * * * root /usr/bin/mysql -uroot -hlocalhost -Dxtream_iptvpro -e"TRUNCATE TABLE client_logs"
0 8 * * * root /usr/bin/mysql -uroot -hlocalhost -Dxtream_iptvpro -e"TRUNCATE TABLE stream_logs"
0 8 * * * root /usr/bin/mysql -uroot -hlocalhost -Dxtream_iptvpro -e"TRUNCATE user_activity"


After editing file simply exit and save by using Ctrl+x and pressing y.

MySQL EVENT Version

Connect to the XtreamUI Database

mysql -u root

Select the XtreamUI Database

use xtream_iptvpro;

Enable the SQL Event Scheduler

SET GLOBAL EVENT_SCHEDULER = ON;

Create the Events to Turncate (empty) the tables stream_logs, user_activity, client_logs every one hour. If you want empty the logs once per Day, just replace EVERY 1 HOUR with EVERY 1 DAY

CREATE EVENT delete_stream_logs ON SCHEDULE EVERY 1 HOUR STARTS NOW() DO TRUNCATE TABLE stream_logs;
CREATE EVENT delete_user_activity_logs ON SCHEDULE EVERY 1 HOUR STARTS NOW() DO TRUNCATE TABLE user_activity;
CREATE EVENT delete_client_logs_logs ON SCHEDULE EVERY 1 HOUR STARTS NOW() DO TRUNCATE TABLE client_logs;

You can list the created Events with:

SHOW EVENTS;




Removing Events

DROP EVENT delete_user_activity_logs;


Exit MySQL Session:

EXIT;