From: Brian Dunning
Date: June 4 2010 3:10pm
Subject: Best way to purge old records from a huge table?
List-Archive: http://lists.mysql.com/mysql/221819
Message-Id: <D62F95BA-0E63-478F-9EB0-9A6680AE6B86@briandunning.com>
MIME-Version: 1.0 (Apple Message framework v1078)
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable

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?=