Clearing clicks from large CPV Labs database

If you have a customer using CPV Labs, they can find the database can grow quite large, and can start slowing things down.  To help alleviate the pressure caused by this, you can clear out the database of old clicks.  Here are a couple of ways to do this.

To delete clicks older than 3 weeks

DELETE FROM clicks WHERE ViewDate<DATE_ADD(now(), INTERVAL -3 week);
DELETE FROM clicksextra WHERE ClickID NOT IN (SELECT ClickID FROM clicks);
DELETE FROM clickslp WHERE ClickID NOT IN (SELECT ClickID FROM clicks);
DELETE FROM clicksopt WHERE ClickID NOT IN (SELECT ClickID FROM clicks);
DELETE FROM subids WHERE DateAdded<DATE_ADD(now(), INTERVAL -6 week);
OPTIMIZE TABLE clicks;
OPTIMIZE TABLE clicksextra;
OPTIMIZE TABLE clickslp;
OPTIMIZE TABLE clicksopt;
OPTIMIZE TABLE subids;

You can change the INTERVAL to 1 week, 2 week, 30 day, etc etc
The INTERVAL used for subids should be greater then the INTERVAL used for the clicks table

 

To delete all clicks for a single campaign

delete from clicks where CampaignID=xxx;
delete from clicksextra where ClickID not in (select ClickID from clicks);
delete from clickslp where ClickID not in (select ClickID from clicks);
delete from clicksopt where ClickID not in (select ClickID from clicks);

To delete clicks older than 3 weeks for a single campaign

delete from clicks where CampaignID=xxx and ViewDate<DATE_ADD(now(), INTERVAL -3 week);
delete from clicksextra where ClickID not in (select ClickID from clicks);
delete from clickslp where ClickID not in (select ClickID from clicks);
delete from clicksopt where ClickID not in (select ClickID from clicks);

 

Note that xxx in the above queries is the ID of the campaign you want to delete clicks for.