From: Ananda Kumar Date: June 4 2010 3:19pm Subject: Re: Best way to purge old records from a huge table? List-Archive: http://lists.mysql.com/mysql/221820 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=000e0cd32c7e19ca29048835da64 --000e0cd32c7e19ca29048835da64 Content-Type: text/plain; charset=ISO-8859-1 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 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=anandkl@stripped > > --000e0cd32c7e19ca29048835da64--