MySQL Forums :: Newbie :: let table grow for two months then delete first month


Advanced Search

let table grow for two months then delete first month
Posted by: Darren White ()
Date: March 20, 2012 04:26AM

Hi - I would appreciate any tips getting an EVENT SCHEDULE going. Not sure it should be an event though so will put it in Newbie.

I have log tables growing at a rate of one row per second (an example of CREATE for one such table below).

I would ideally like to run an EVENT that would check the timestamps on this column periodically and when it reaches 2 months of data (5.4 million rows), then delete the first months worth of data.

Any tips to get me started?

I imagined something like this:

CREATE EVENT tidy_logs
ON SCHEDULE
EVERY 1 MONTH
STARTS (NOW() + INTERVAL 1 MINUTE)
DO
BEGIN

SELECT @firstDate := MIN(last_update) FROM log_streamer_01;
SELECT @lastDate := MAX(last_update) FROM log_streamer_01;
IF @lastDate - @firstDate >= 2 MONTHS THEN -- unsure of syntax
DELETE --the first month. mot sure how to do this best on a table with 6million rows

-- repeat for other tables

END$$

--

CREATE TABLE `log_streamer_01` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`streamer_tension` float NOT NULL DEFAULT '0',
`cable_speed` float NOT NULL DEFAULT '0',
`reel_speed_rpm` float NOT NULL DEFAULT '0',
`meter_out` float NOT NULL DEFAULT '0',
`synchro_delta_deviation` float NOT NULL DEFAULT '0',
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=54337 DEFAULT CHARSET=latin1$$

Options: ReplyQuote


Subject Written By Posted
let table grow for two months then delete first month Darren White 03/20/2012 04:26AM
Re: let table grow for two months then delete first month irek kordirko 03/20/2012 08:45AM
Re: let table grow for two months then delete first month Darren White 03/22/2012 02:38AM
Re: let table grow for two months then delete first month irek kordirko 03/23/2012 04:47PM
Re: let table grow for two months then delete first month Darren White 03/26/2012 02:07AM
Re: let table grow for two months then delete first month Darren White 03/26/2012 02:10AM
Re: let table grow for two months then delete first month Rick James 03/27/2012 09:04PM
Re: let table grow for two months then delete first month Darren White 04/03/2012 01:13AM
Re: let table grow for two months then delete first month Rick James 04/04/2012 06:22PM
Re: let table grow for two months then delete first month Darren White 04/10/2012 01:18AM
Re: let table grow for two months then delete first month Rick James 04/12/2012 09:15AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.