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 10000; 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]
