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=sh...@mysql.com
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
