You can use the LIMIT clause to break up a long DELETE into a series of shorter ones,
executed in a loop.
So, in pseudo-code:
Set delete_row_max = 1000 (or another appropriate value)
do
{
DELETE FROM outgoing
WHERE timestamp < last_time
LIMIT delete_row_max;
row_count = no. of affected rows returned by MySQL
(other processing, perhaps a sleep)
} while (row_count >= delete_row_max;
-----Original Message-----
From: Viraj Alankar [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 9:57 AM
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
