From: Daevid Vincent
Date: October 25 2012 6:46pm
Subject: Help with purging old logs for each customer ID
List-Archive: http://lists.mysql.com/mysql/228487
Message-Id: <0b1001cdb2e1$0074c210$015e4630$@daevid.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

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.