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:dae...@daevid.com]
> Sent: Thursday, October 25, 2012 1:33 PM
> To: Rick James; mysql@lists.mysql.com
> 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:rja...@yahoo-inc.com]
> > Sent: Thursday, October 25, 2012 1:09 PM
> > To: Daevid Vincent; mysql@lists.mysql.com
> > 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:dae...@daevid.com]
> > > Sent: Thursday, October 25, 2012 11:46 AM
> > > To: mysql@lists.mysql.com
> > > 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
