Well, the customer_id is relevant in that I want the last 90 days relative
to each customer.
customer_id = 123 might have logs from jan - mar
customer_id = 444 might have logs from feb - may
So it's a rolling log of THEIR last 90 days from their last log (most
recent) back 90 days from there.
Does that make more sense?
I guess I was trying to avoid looping over every customer ID and computing
if I could help it. I thought by using a GROUP BY or something it could
group all the logs for a given customer and then trim them that way.
But maybe brute force is the way to go?
> -----Original Message-----
> From: Rick James [mailto:rjames@stripped]
> Sent: Thursday, October 25, 2012 1:09 PM
> To: Daevid Vincent; mysql@stripped
> Subject: RE: Help with purging old logs for each customer ID
> Off hand, I would iterate over the PRIMARY KEY, looking at a thousand rows
> at a time, DELETEing any that need to be purged. I would use a Perl or
> loop, or write a stored procedure. More discussion of "huge deletes"
> this _could_ be):
> (PARTITIONing does not apply in your case, as I understand it.)
> I like the loop:
> SELECT @z := customer_log_id FROM customers_log LIMIT 1000, 1;
> DELETE FROM customers_log
> WHERE customer_log_id >= @left_off AND customer_log_id < @z
> AND created_on < NOW() - INTERVAL 90 DAY;
> sleep a few seconds (to be a nice guy)
> Plus code to take care of iterating and terminating.
> That loop could be done continually.
> It seems that customer_id is irrelevant??
> > -----Original Message-----
> > From: Daevid Vincent [mailto:daevid@stripped]
> > Sent: Thursday, October 25, 2012 11:46 AM
> > To: mysql@stripped
> > Subject: Help with purging old logs for each customer ID
> > 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','Genera
> > l','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.
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql