Welcome to DB hell, I assume you are using MySQL. Using foreign keys and cascading deletion will create giant locks causing major performance issues if your DB is under heavy load. Your reads will lock and everything can potentially fall apart :(
Mu suggestion if you want to a our crappy performance is to remove the fk constraints and create an asynchronous job to delete the related records on deletion. Delete your user and trigger a job with the player id as a reference and let the job delete the associated records & data. Of course that's not the ideal solution but it should help. - Matt Sent from my iPhone On Oct 25, 2010, at 8:17, 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
