perhaps you can run this more frequently, say once an hour if it does not interfere with your application logic.
> -----Original Message----- > From: Viraj Alankar [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, April 02, 2002 4:57 PM > To: [EMAIL PROTECTED] > Subject: help optimizing log table deletes > > > Hello, > > We have an 'outgoing' table being used to store email header information > defined as follows: > > CREATE TABLE outgoing ( > rpath varchar(80) default NULL, > auth varchar(80) NOT NULL default '', > ip varchar(80) default NULL, > hfrom varchar(80) default NULL, > hto varchar(80) default NULL, > subject varchar(80) default NULL, > messageid varchar(80) default NULL, > timestamp timestamp(14) NOT NULL, > rcpts smallint(5) unsigned default '0', > KEY at_ind (auth,timestamp,rcpts) > ) TYPE=MyISAM; > > On average we have about 300k records in this table. Every day we archive > data older than 7 days to another table, with a query similar to: > > $last_time = select now() - interval 7 day; > > insert into newtable select * from outoing where timestamp < '$last_time'; > delete from outgoing where timestamp < '$last_time'; > > The problem is the delete query can take up to 1 minute to execute. This > prevents any other inserts from taking place since the table is locked. > > I was wondering how I can design this better to avoid this problem. My thought > was to go to InnoDB but this seems overkill for this slight issue. > > sql > > Viraj. > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
