fast way to delete records?

[ylu]

Hi there,

I have a MyISAM table, to which records are inserted every second:

CREATE TABLE `XYZPOS` (
`site` varchar(4) NOT NULL default '0',
`DTIME` datetime NOT NULL default '0000-00-00 00:00:00',
`X` double NOT NULL default '0',
`Y` double NOT NULL default '0',
`Z` double NOT NULL default '0',
PRIMARY KEY (`site`,`DTIME`),
KEY `DSINX` (`DTIME`,`site`)
) TYPE=MyISAM COMMENT='Site XYZ POS'



The 'site' will have at least 10 different site names. You can see that
the table get filled up very fast. I need to periodically delete records
after the records are one week older. The problem is that the deletion takes
a long time (usually more than 10 seconds), and I need to insert records
every seconds at the same time. How can I speed up the deletion process?
( the deletion and insertion are done by different programs), and how to handle
the deletion/insertion simultaneously?

I use "delete from XYZPOS where DTIME<=DATE_SUB(NOW(), INTERVAL 7 DAY)"
to delete records.


Many thanks.

Yuan



Edited 2 time(s). Last edit at 05/23/2006 04:01PM by ylu.