469,950 Members | 2,370 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,950 developers. It's quick & easy.

Achieving faster "deletes"

mysql 4.0.22 (win32)

Can anyone recommend best practices for the fastest way to remove large
numbers of rows at once?
I am diving my deletes into chunks (1000 rows at a time) but this still is
very slow.

-Craig
Jul 20 '05 #1
2 1696
Craig Stadler wrote:
mysql 4.0.22 (win32)

Can anyone recommend best practices for the fastest way to remove large
numbers of rows at once?
I am diving my deletes into chunks (1000 rows at a time) but this still is
very slow.


Depends what you need to remove.

If you want to remove all rows:
delete from tablename;

If you want to remove all rows which have the flag delete set as 1
delete from tablename where glag=1;

If you have for example certain master-items that you have removed and
now you want to remove all items related to those according to master_id:
delete from tablename where master_id in(1,13,44,45,66);

If you want to delete all rows where creation_date is older than a year:
delete from tablename where creation_date < '2003-12-01';

All these are very fast the the column used in the query is indexed.

Followups only to mailing.database.mysql, since this has nothing win32
specific.
Jul 20 '05 #2
Craig Stadler wrote:
mysql 4.0.22 (win32)

Can anyone recommend best practices for the fastest way to remove large
numbers of rows at once?
I am diving my deletes into chunks (1000 rows at a time) but this still is
very slow.

-Craig


Are your DELETEs based on a primary key or a non-indexed column?
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

18 posts views Thread by Leslaw Bieniasz | last post: by
15 posts views Thread by Bobby C. Jones | last post: by
4 posts views Thread by papaja | last post: by
30 posts views Thread by Medvedev | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.