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','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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
