List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:June 4 2010 5:20pm
Subject:Re: Best way to purge old records from a huge table?
View as plain text  
Brian Dunning 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
> 

My idea is to create a new table with just the data you want to keep and 
drop the old one. Every batch you delete must update the indexes on the 
existing table. Creating a new,smaller, batch of data with a fresh set 
of indexes  should be much faster than incrementally deflating the 
existing huge set of data.

Once the new table is created, use a RENAME TABLE to swap both table 
names to put the new table into the old one's place and to give the old 
table a name you can work with later.

-- 
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
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