List:General Discussion« Previous MessageNext Message »
From:Krishna Chandra Prajapati Date:June 4 2010 3:39pm
Subject:Re: Best way to purge old records from a huge table?
View as plain text  
Hi Brian,

I would suggest you to use mk-archiver (Maatkit Tools) for this activity.

http://www.percona.com/files/presentations/Make_Life_Easier_Maatkit_v2.pdf

Regards,
Krishna

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