Cleaning up the database: campaign_log table

[hkphooey]

Hi,

A client is running a 4.5.1 install of Sugar CRM and the campaign_log table has ballooned to 300 Mb. I figure a lot of this can be cut out ...

First of all, I can't find any documentation anywhere, but I gather the Campaign log tracks events associated with campaigns:

• Sending out emails
• Link Clickthroughs
• Unsubscribe clickthroughs etc.

Around a third of the campaigns have been deleted (deleted=1) but still remain in the database. I propose to run some SQL as follows against the database at regular intervals. Will this break anything? Have I made some wrong assumptions?

// Clean Campaigns up
-- Remove all Deleted campaigns older than 20 days.
DELETE * FROM `campaigns` where `deleted`=1  AND date_modified <= (now() - INTERVAL 20 DAY);
-- Remove entries from all associated tables which now no longer have a corresponding entry in campaigns
DELETE * FROM `campaign_log` WHERE campaign_id NOT IN (SELECT id FROM `campaigns`);
DELETE * FROM `campaign_trkrs` WHERE campaign_id NOT IN (SELECT id FROM `campaigns`);
-- Optimize tables to shrink the database
OPTIMIZE TABLE campaigns, campaign_log, campaign_trkrs;

Have I missed any tables from this?

PS. Basing this on knowledge from http://www.sugarcrm.com/wiki/index.p...Data_and_Files and will update that page with any info gained from this post.

[hkphooey]

OK, if no-one can help me out with this, maybe I can ask it in a different way. Does anyone know where there is documentation on the database structure which will help me figure this out?

Or does one of the developers who works on the campaign modules have time to explain this to me?

Previous databases I've worked with have had triggers built in, which when you delete a record ... contact, email, campaign etc will go through and remove all the related data in other tables. Seems like this would be a good idea with Sugar as well.