CODEWITHSUNDEEP
mysqlruby-on-railsruby
Abass Sesay
Abass Sesay

Reputation: 951

Rake task that purge just a single table

Is there a Rake command that i can run that would just delete all the fields of a table instead of dropping the whole database and recreating it. I have a table that grows large very quick, most of the data in it does not need to persist more that a week.

Upvotes: 0

Views: 239

Answers (2)

Alex
Alex

Reputation: 258

If you need to remove old records from the table, without deleting current data, you need to be careful not to issue SQL statements that could lock up a large % of the records in your table. (It sounds like this table is written to frequently, so locking it for a long time is not acceptable.)

At least with mysql+innodb, you can easily end up locking more rows than just the ones you actually delete. See http://mitchdickinson.com/mysql-innodb-row-locking-in-delete/ for more info on that.

Here's a process that should keep the table fairly available, and let you remove old rows:

  1. Select just the ids of a set of records which you want to remove, based on their created_at times.
  2. Issue a DELETE for those records.
  3. Repeat this process as long as the SELECT returns records.

This gives you an idea of the process...

max_age = 7.days.ago
batch_size = 1000
loop do
  ids = Model.select(:id).
    where('created_at < ?', max_age).
    limit(batch_size).
    map(&:id)
  Model.where(id: ids).delete_all
  break if ids.size < batch_size
end

Since the SELECT and the DELETE are separate statements, you won't lock any records which aren't actually being removed. The overall time taken for this process will definitely be longer than a simple TRUNCATE TABLE, but the benefit is you'll be able to keep recent records. TRUNCATE will remove everything.

Upvotes: 0

jlhonora
jlhonora

Reputation: 10699

Try truncating the table:

ActiveRecord::Base.connection.execute("TRUNCATE TABLE table_name")

From MySQL's docs:

Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements.

Which seems like what you want to achieve.

Upvotes: 2

Related Questions