I need to delete 200,000 rows from an 8 million row MyISAM table using an
index. It's taking 20 minutes which is quite slow. Is there any way to
speed it up? TIA
Mike
MySQL 4.1.10

Is the table heavily indexed? Indexes cause inserts and deletes (and updates under certain conditions) to slow down.

Also, since you are deleting approx 2.5% of your rows, your delete-statement must have a where-clause. You might want to do an
explain-plan on it to make sure it's not an optimization issue.

I would look at your disk drives and their interface (ATA, SATA, SCSI). I'm sure there is a fair bit of disk-writing going on.

You can try to drop some un-needed indexes (if there are any) and see if that helps. You could also drop all indexes except the
one with the least cardinality in the where-clause of your delete script. That might not be feasible, however.

Finally, try converting the table to InnoDB (you'll need to do some configuration in your my.cnf) - it tends to perform better in
circumstances such as yours. I would do this on a test server first.

David

mos wrote:
I need to delete 200,000 rows from an 8 million row MyISAM table using
an index. It's taking 20 minutes which is quite slow. Is there any way
to speed it up? TIA

Mike
MySQL 4.1.10

You should try deleting in smaller batches if you can. I've found things go a lot quicker doing it that way and you don't lock up
your table for long durations if you want to stick with MyISAM.

Message
From: "mos" <mos99 (AT) fastmail (DOT) fm>
To: <mysql (AT) lists (DOT) mysql.com>
Sent: Wednesday, June 21, 2006 5:55 PM
Subject: Need to speed up deletes


>I need to delete 200,000 rows from an 8 million row MyISAM table using an index. It's taking 20 minutes which is quite slow. Is
>there any way to speed it up? TIA
>
Mike
MySQL 4.1.10
--