MySQL or Postgres? Can you check the schema to make sure the FK has ON DELETE CASCADE?
If MySQL, I know that it becomes slow for cascading when you have 1Mi child. Another thing to look on is if there is a lot of index regeneration happening after that delete. That could easly be the slow down. I think you should also think about marking them with the delete_me and clean them later. Here is some more info on it: http://www.listsearch.com/MySQL/Thread/index.lasso?84356#377241 BTW, pretty cool job on the www.sproutrobot.com. On Mon, Oct 25, 2010 at 8:17 AM, Erik Pukinskis <[email protected]>wrote: > Hey all, > > I've been trying find a way to set up relatively fast cascading of > deletions across multiple model associations with no luck. It seems > like most people just throw :dependent => :destroy into their models, > but that's dog slow, especially if you've got a big table with lots of > rows (I have a 100k item table joined to a 1m item table, and I need > to delete a large number of those). > > So obviously I need to do this in the database, but I can't really > find any way to do it. I've got a User who has a Garden who has many > Crops. And I've got the following in my migrations: > > add_foreign_key :gardens, :user_id, :users, :id, :on_delete => > :cascade, :name => "gardens_user_fkey" > add_foreign_key :crops, :garden_id, :gardens, :id, :on_delete => > :cascade, :name => "crops_garden_fkey" > > That's using the redhillonrails_core plugin > (http://github.com/weplay/redhillonrails_core). I thought that would > do it, but when I delete a user, the garden and crops stick around. > > There's not much documentation on the web for this issue.... seems > like most people are only deleting a row or two in their apps, and > just use the ActiveRecord facilities and accept that it'll take a > little bit of time. > > Anyone else have any experience with this? > > Best, > Erik > > -- > SD Ruby mailing list > [email protected] > http://groups.google.com/group/sdruby -- SD Ruby mailing list [email protected] http://groups.google.com/group/sdruby
