Re: Need to speed up deletes

[Brent Baisley]

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.


- Original Message - 
From: mos [EMAIL PROTECTED]

To: mysql@lists.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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Need to speed up deletes

[mos]

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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Need to speed up deletes

[David Griffiths]

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




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]