Hi,
You can do it in several smaller and faster deletes using the LIMIT option -
for example
DELETE QUICK from table WHERE indexed_row UNIX_TIMESTAMP()-86400 limit
1;
HTH
Dobromir Velev
[EMAIL PROTECTED]
- Original Message -
From: Chris Elsworth [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, December 05, 2003 12:42
Subject: DELETE on a huge table; how long *should* it take?
Hello,
I have quite a large table, 45 million rows, which has 3 indexes on
it. The rows are evenly distributed across one particular index, which
records the time the row was inserted. At any given time there's
between 20 and 21 days worth of rows, and every night I delete
anything over 20 days. So I'm deleting about 2.2 million rows, with
what is basically:
DELETE QUICK from table WHERE indexed_row UNIX_TIMESTAMP()-86400
I have PACK_KEYS=1 DELAY_KEY_WRITE=1, and this ia MyISAM table. Now,
roughly, should this take half an hour or more? It seems very disk
bound, producing lots of small disk transactions. I wouldn't really
mind, but the entire table is locked for the process and the site it's
powering grinds to a halt.
My first thought is to change it to InnoDB and use a transaction so
the delete can take as long as it wants without interrupting anything
else. I am however I bit worried about space; the MyISAM files are
using 5G for data + 763M for index; it's only an 18G drive thus I'm a
bit worried the InnoDB equivalent is going to be too big.
Any other pointers, speedup tips, ways to avoid this issue entirely?
--
Chris
--
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]
