List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:October 25 2012 11:15pm
Subject:RE: Help with purging old logs for each customer ID
View as plain text  
Thank you Rick! You're a super freak! ;-p

This gets me in the vicinity of where I'm trying to go and I learned a few
new tricks with the StartLoop: stuff too! Neat!

d.

> -----Original Message-----
> From: Rick James [mailto:rjames@stripped]
> Sent: Thursday, October 25, 2012 1:54 PM
> To: Daevid Vincent; mysql@stripped
> Subject: RE: Help with purging old logs for each customer ID
> 
> If the 90 days is back from MAX(created_on) for a given customer...
>    INDEX(customer_id, created_on)
> will probably be needed.  And that should replace KEY `customers_id`
> (`customer_id`).
> 
> Maybe...
> 
> DELETE FROM customers_log AS a
>     WHERE a.customer_log_id >= @left_off AND a.customer_log_id < @z
>       AND a.created_on <
>              ( SELECT MAX(created_on) FROM customers_log
>                  WHERE customer_id = a.customer_id
>              )
>               - INTERVAL 90 DAY );
> (Since this has the subquery, I would do only 100 at a time, not 1000)
> 
> Or...
> CREATE TEMPORARY TABLE tmp
>     SELECT customer_id, MAX(created_on) - INTERVAL 90 DAY  AS cutoff
>         FROM customers_log
>         GROUP BY customer_id;
> DELETE FROM customers_log AS a
>        JOIN tmp  ON a.customer_id = tmp.customer_id
>     WHERE a.customer_log_id >= @left_off AND a.customer_log_id < @z
>       AND a.created_on < tmp.cutoff;
> 
> If you have millions of rows, a delete without some kind of loop is asking
> for trouble.
> 
> Or...
> Turning things around to base it on customers...
> Loop through customer_ids (yeah, you did not want to do this)
> 
> SELECT @id := 0;
> StartLoop:
>     SELECT @id := customer_id WHERE customer_id > @id ORDER BY customer_id
> LIMIT 1;
>     if @id is NULL, exit
>     DELETE FROM customers_log AS a
>         WHERE a.customer_id = @id
>           AND a.created_on <
>              ( SELECT MAX(created_on) FROM customers_log
>                  WHERE customer_id = @id
>              )
>               - INTERVAL 90 DAY );
> EndLoop.
> 
> Since there is no rush for the purging, there is little need to optimize
it
> other than to keep it from interfering with other queries.  To that end,
the
> compound index I propose is important.
> 
> > -----Original Message-----
> > From: Daevid Vincent [mailto:daevid@stripped]
> > Sent: Thursday, October 25, 2012 1:33 PM
> > To: Rick James; mysql@stripped
> > Subject: RE: Help with purging old logs for each customer ID
> >
> > 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','Gen
> > > > era
> > > > 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