CODEWITHSUNDEEP
mysqlruby-on-rails-4
user2452057
user2452057

Reputation: 906

deleting millions of records in rails

Our RoR 4 app has millions of records which needs to be deleted on a regular basis. Currently the deletion happens as a backgound job: 

 while fruit.apples.count > 0
      # create the sql query to delete 1000 feeds from the channel
      sql = "DELETE FROM apples WHERE fruit_id=#{fruit_id} LIMIT 1000"
      # execute the sql query
      ActiveRecord::Base.connection.execute(sql)
      # wait a bit before the next delete
      sleep 0.1
 end

because I am doing a count every few seconds, it has spiked the mysql server's cpu time. So was wondering if I could delete a million records using delete_all/destroy_all or are there any better way of achieving this.

Upvotes: 3

Views: 613

Answers (1)

Bernd Buffen
Bernd Buffen

Reputation: 15057

You can use TRUNCATE instead of DELETE. TRUNCATE deletes the whole table and recreates an empty table. So this is much faster. TRUNCATE also resets the value of AUTO INCREMENT field in the table

TRUNCATE TABLE yourTable;

Upvotes: 1

Related Questions