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]
