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 <br...@briandunning.com>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=prajapat...@gmail.com > >
