List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:October 25 2012 6:46pm
Subject:Help with purging old logs for each customer ID
View as plain text  
I have a customer log table that is starting to rapidly fill up (we have
hundreds of thousands of users, but many are transient, and use the service
for a few months, or use the free trial and quit, etc.)

CREATE TABLE `customers_log` (
  `customer_log_id` bigint(20) unsigned NOT NULL auto_increment,
  `customer_id` int(10) unsigned default '0',
  `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
  `type`
enum('View','Action','Admin','Search','Login','Logout','Access','General','A
PI'),
  `source` enum('web','mobile','system'),
  `body` text,
  PRIMARY KEY  (`customer_log_id`),
  KEY `created_on` (`created_on`),
  KEY `customers_id` (`customer_id`)
) ENGINE=InnoDB

What I'd like to do now is make a 'rolling log' in that I want to DELETE any
entries older than 90 days for EACH `customer_id`.

I'm not sure how to do that in a query? I'd rather not iterate over each
customer_id if I can help it.



Thread
Help with purging old logs for each customer IDDaevid Vincent25 Oct
  • RE: Help with purging old logs for each customer IDRick James25 Oct
    • RE: Help with purging old logs for each customer IDDaevid Vincent25 Oct
      • RE: Help with purging old logs for each customer IDRick James25 Oct
        • RE: Help with purging old logs for each customer IDDaevid Vincent25 Oct