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:[email protected]]
> Sent: Thursday, October 25, 2012 11:46 AM
> To: [email protected]
> 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
