mysql - Why is ActiveRecord destroy_all taking so long? -

i have simple rails app articles , comments running on mysql 5.5, ruby 1.9.3 , rails 3.2.12:

class article < activerecord::base                                                                                      attr_accessible :body, :title   has_many :comments end     class comment < activerecord::base   attr_accessible :content   belongs_to :article end 

i have generated lots of comments article, , trying delete them in rails console:

$ rails c  loading development environment (rails 3.2.12) [1] pry(main)> = article.find(1)    (2.0ms)  set sql_auto_is_null=0   article load (8.0ms)  select `articles`.* `articles` `articles`.`id` = 1 limit 1 => #<article id: 1, title: "test", body: "---\n- est vel provident. laboriosam dolor asperiore...", created_at: "2013-05-17 09:54:54", updated_at: "2013-05-21 14:52:18"> [2] pry(main)> require 'benchmark' [3] pry(main)> puts benchmark.measure { a.comments.destroy_all }   comment load (896.0ms)  select `comments`.* `comments` `comments`.`article_id` = 1   explain (2.0ms)  explain select `comments`.* `comments` `comments`.`article_id` = 1 explain for: select `comments`.* `comments`  `comments`.`article_id` = 1 +----+-------------+----------+------+---------------+------------+---------+-------+-------+-------------+ | id | select_type | table    | type | possible_keys | key        | key_len | ref   | rows  |       | +----+-------------+----------+------+---------------+------------+---------+-------+-------+-------------+ |  1 | simple      | comments | ref  | article_id    | article_id | 5       | const | 48186 | using | +----+-------------+----------+------+---------------+------------+---------+-------+-------+-------------+ 1 row in set (0.00 sec)    sql (1.0ms)  delete `comments` `comments`.`id` = 2   sql (2.0ms)  delete `comments` `comments`.`id` = 3   sql (1.0ms)  delete `comments` `comments`.`id` = 4   sql (1.0ms)  delete `comments` `comments`.`id` = 5   sql (1.0ms)  delete `comments` `comments`.`id` = 6   sql (5.0ms)  delete `comments` `comments`.`id` = 7   sql (2.0ms)  delete `comments` `comments`.`id` = 8   sql (2.0ms)  delete `comments` `comments`.`id` = 9  . . .   sql (0.0ms)  delete `comments` `comments`.`id` = 37360   sql (0.0ms)  delete `comments` `comments`.`id` = 37361 

the last query deleting last comment, , process hangs there very long time before returning , committing:

   (1.9ms)  commit 690.380000   1.390000 691.770000 (693.885877) 

show processlist confirms there no lock:

mysql> show processlist; +----+----------+-----------+------------------+---------+------+-------+------------------+ | id | user     | host      | db               | command | time | state | info             | +----+----------+-----------+------------------+---------+------+-------+------------------+ |  6 | bloguser | localhost | blog_development | query   |    0 | null  | show processlist | |  7 | bloguser | localhost | blog_development | sleep   |  459 |       | null             | +----+----------+-----------+------------------+---------+------+-------+------------------+ 2 rows in set (0.00 sec) 

delete_all dependent: :destroy or dependent: :delete_all show similar behaviour.

the popular belief seems issue destroy_all instantiates objects , deletes them 1 one, doesn't it's problem here. taking long process after deletes have been executed, , before commit called?

digging deeper, seems deletion comments array takes long. records deleted removed array here.

simulating large array, same slow behaviour:

1.9.3-p194 :001 > require 'benchmark'; require 'ostruct'  => true  1.9.3-p194 :002 > = 0; = []  => []  1.9.3-p194 :003 > 35_000.times { i+=1; << openstruct.new(value: i) }  => 35000  1.9.3-p194 :004 > puts benchmark.measure { a.each { |i| a.delete(i) } } 623.560000   0.820000 624.380000 (625.244664) 

activerecord optimized array#clear in case of destroy_all...