DATABASE Maintainance

Here is what I do monthly at several of our larger clients:

Create archive tables without auto-increment in the primary index fields, then run these queries

SELECT count(*) from call_log;
SELECT count(*) from call_log_archive;
INSERT IGNORE INTO call_log_archive SELECT * from call_log;
DELETE FROM call_log WHERE start_time < '2008-03-01 01:00:00';
optimize table call_log;
DELETE from call_log_archive where channel LIKE"Local/9%" and extension not IN(' 8365','8366','8367','8368') and caller_code LIKE "V%" and length_in_sec < 75 and start_time < '2008-03-01 01:00:00';
optimize table call_log_archive;


SELECT count(*) from vicidial_log;
SELECT count(*) from vicidial_log_archive;
INSERT IGNORE INTO vicidial_log_archive SELECT * from vicidial_log;
DELETE FROM vicidial_log WHERE call_date < '2008-03-01 01:00:00';
optimize table vicidial_log;
optimize table vicidial_log_archive;

SELECT count(*) from server_performance;
DELETE FROM server_performance WHERE start_time < '2008-03-01 01:00:00';
optimize table server_performance;

SELECT count(*) from vicidial_agent_log;
SELECT count(*) from vicidial_agent_log_archive;
INSERT IGNORE INTO vicidial_agent_log_archive SELECT * from vicidial_agent_log;
DELETE FROM vicidial_agent_log WHERE event_time < '2008-03-01 01:00:00';
optimize table vicidial_agent_log;
optimize table vicidial_agent_log_archive;