SQL video_history table 100MB after 18 days

Eri · 2011-05-20 07:54:22

Making a review on my SQL after 18 days with ASPro I have noteced that the video_history table is up to 100MB.
At the moment there is no problem but after 180 days that table will be 1GB if site have the same traffic.
You think adding a php cron file to delete all entries in that table every month will handle the problem.

symtab · 2011-05-20 09:05:08

That could be a solution. Or remove and move older queries to another table, just so that admins can see who watched their videos.

<?php
defined('_VALID') or die('Restricted Access!');
require '../libraries/bootstrap.php';

set_time_limit(0);

$db = VF::factory('database');
$db->query("DELETE FROM #__video_history
            WHERE view_date < '".date('Y-m-d h:i:s', strtotime('10 days ago'))."'");

VF::close();
?>

symtab · 2011-05-20 09:06:00

You can also disable that if you want from modules/video/components/view.php (just delete the lines that insert data into video_history). Its only used to keep track of video views (you can do that with google analytics also). Reporting in admin for video views will not work anymore.

Pikeypete · 2011-05-20 14:02:57

Good topic.
So it's basically just the one line?

$this->db->query("INSERT INTO #__video_history

Will certainly free up the SQL a little, like you said there's always analytics or Woopra which is what I use for tracking.

Coscast · 2011-05-20 14:33:44

mysql --user=yoursqlusername --password=yourpassword --database=yourdatabase -e 'TRUNCATE TABLE video_history'

Crontab that

Did that on my box too.

symtab · 2011-05-20 15:19:57

Yeah. Its only the one line adding data to video_history. If you remove that line you will not have the statistics in admin dashboard.

Adult Script Pro Community Forums

#1 2011-05-20 07:54:22

SQL video_history table 100MB after 18 days

#2 2011-05-20 09:05:08

Re: SQL video_history table 100MB after 18 days

#3 2011-05-20 09:06:00

Re: SQL video_history table 100MB after 18 days

#4 2011-05-20 14:02:57

Re: SQL video_history table 100MB after 18 days

#5 2011-05-20 14:33:44

Re: SQL video_history table 100MB after 18 days

#6 2011-05-20 15:19:57

Re: SQL video_history table 100MB after 18 days

Board footer