I've had to delete large amounts of records from tables with over 200 millions records. You are correct in that you want to do it in
chunks. How you divide the deletes is up to you to decide. You would want to do it based on a indexed column, like a date, and
specify a range.
DELETE data
FROM data, event
WHERE data.cid=event.cid
AND event.timestamp between "2006-01-01" and "2006-01-07"
DELETE data
FROM data, event
WHERE data.cid=event.cid
AND event.timestamp between "2006-01-08" and "2006-01-15"
...
You'l find it will go much, much quicker if you can narrow down the number of records deleted at one time. A delete that I would
give up on after a few hours take a few minutes by doing it in chunks.
BUT, if your data is always going to be divided into date chunks where you'll periodically delete everything prior to a certain
date, you may want to look into merge tables. Typically a merge table is a psuedo table that is comprised of a set of underlying
tables. You can add/drop underlying tables very quickly, or you could even reference an underlying table directly for certain tasks.
Under the right circumstances, merge tables can provide considerable amount of scalability.
----- Original Message -----
From: "Jacob, Raymond A Jr" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Sunday, July 09, 2006 10:36 PM
Subject: How does one speed up delete-Again
I started the operation below on Friday at 1300hrs EST
DELETE data
FROM data, event
WHERE data.cid = event.cid
AND event.timestamp < "2006-05-01"
It is now Sunday 22:00hrs EST and the operation is still running.
Question: Should it take this long to delete 7.5 million records from a
4.5GB
Table?
Question: Other than writing a script to export all the cid's to a file
and deleting the records one at a time so at least I can delete some
records.
Is there a way to delete records one at a time or in groups
so that if I have to stop the operation the delete will not rolled back?
Question:Does anyone on the list have experience deleting what I guess
is a large number of
records from a large table? i.e. how long does it take?
r/Raymond
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
