quicker execution of DELETE queries on large tables

mysql

[dougmcc1]

Delete queries take a long time to run and I'm looking for ways to speed them up without having to delete or move data.

I usually delete rows where the date field is equal to a particular date. So my query looks like this:

DELETE FROM my_table WHERE date='2007-04-20'

My tables generally have from 800,00 to 1,200,000 rows and it takes about 20 minutes for this query to run even though the date field is indexed. Does that sound slow? Is there anything else I can do to speed it up?

Thanks.

[coopster]

Yes, that does seem slow. But, that all depends on the number of indexes you have on the table too, not just having that particular column indexed. There is at least one tip regarding the Speed of DELETE Statements [dev.mysql.com].