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:rja...@yahoo-inc.com] > Sent: Thursday, October 25, 2012 1:54 PM > To: Daevid Vincent; mysql@lists.mysql.com > 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: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
