List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:October 25 2012 8:32pm
Subject:RE: Help with purging old logs for each customer ID
View as plain text  
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
PHP
> loop, or write a stored procedure.  More discussion of "huge deletes"
(which
> this _could_ be):
>     http://mysql.rjweb.org/doc.php/deletebig
> (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

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