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 <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=anan...@gmail.com > >
