Hi Richard, As I think Gerald Clark said, you could run DELETEs with LIMITs (like 1000-10000, etc. at a time) in a loop until rows all rows are deleted. This won't make the deletes any faster (probably slightly slower total, actually), but will allow other clients to use the table in between.
Have you seen this page in the manual: http://www.mysql.com/doc/en/Delete_speed.html What's the size of your key_buffer? Might want to increase it. Also make sure the table doesn't have any unnecessary indexes to make DELETEs slower. Hope that helps. Matt ----- Original Message ----- From: <[EMAIL PROTECTED]> Sent: Wednesday, November 19, 2003 4:12 AM Subject: very slow delete queries - never ending > I have a problem with a bigger table on mysql 4.0.16-log / debian linux > > I played around with indexes, delete quick and such, but I just can't get it > to work. > The following table holds >35mio rows and has >5mio inserts/replaces per > day. to clean it up I want to delete all rows older than X days. > > I would be very happy if somebody could help me on this. I'm stuck. I worked > with tables of that size with 3.23.49-log and didn't have problems, although > I must say that the amount of inserts is very high in this case. > > The server is a 2.5ghz pentium4, 1.5gb RAM, SCSI-RAID-disks and such > hardware, so performance should not be a problem. what variables in mysql should I > modify, has anybody experience with that and can > help? > > thanks! > Richard [snip] > mysql> select count(*) from datatable where acttime < '2003-11-14 > 09:39:49'; > +----------+ > | count(*) | > +----------+ > | 7194367 | > +----------+ > 1 row in set (3 min 22.15 sec) > > mysql> select count(*) from datatable; > +----------+ > | count(*) | > +----------+ > | 36003669 | > +----------+ > 1 row in set (5.87 sec) > > mysql> delete quick from datatable where acttime < '2003-11-14 09:39:49'; > or > mysql> delete from datatable where acttime < '2003-11-14 09:39:49'; > > ...takes forever. I killed it after 20 hours... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
