List:General Discussion« Previous MessageNext Message »
From:Ananda Kumar Date:June 4 2010 3:19pm
Subject:Re: Best way to purge old records from a huge table?
View as plain text  
dont use a single delete statment.
Use a stored proc, loop through and delete record by record and commit for
every 10k. In this way, your mysql will not hang and if you replication
setup, slave also will not lag behind.

regards
anandkl

On Fri, Jun 4, 2010 at 8:40 PM, Brian Dunning <brian@stripped>wrote:

> Hey all -
>
> I have a table with 12,000,000 records spread over about 6 years. I'm
> trying to delete all but the last 2 years, but no matter how small of a
> group I try to delete at a time, it keeps hanging up the server and I
> eventually have to restart MySQL. The table looks like this:
>
>  `creation` timestamp NOT NULL default CURRENT_TIMESTAMP on update
> CURRENT_TIMESTAMP,
>  `lat` double NOT NULL default '0',
>  `lon` double NOT NULL default '0',
>  `referer` int(12) NOT NULL default '0',
>  PRIMARY KEY  (`referer`,`lat`,`lon`),
>  KEY `creation` (`creation`,`referer`)
>
> And the query I've been trying looks like this:
>
> delete from tablename where `creation` < '2006-04-01 00:00:00'
>
> ...trying to do the oldest 1 month of records at a time. So am I just
> trying a really inefficient query? Is there a better way to do this?
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>

Thread
Best way to purge old records from a huge table?Brian Dunning4 Jun
  • Re: Best way to purge old records from a huge table?Ananda Kumar4 Jun
  • Re: Best way to purge old records from a huge table?Krishna Chandra Prajapati4 Jun
  • RE: Best way to purge old records from a huge table?Martin Gainty4 Jun
    • Re: Best way to purge old records from a huge table?Johan De Meersman4 Jun
  • Re: Best way to purge old records from a huge table?Shawn Green4 Jun