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