[myexperiment-hackers] Adding indexes to the activity_logs table to improve query performances

[Jiten Bhagat]

Hi,

This is hugely relevant to the event logging work that Sergey and I did
for myExperiment (what's happening with that anyways?)

If you haven't already, I suggest adding a few indexes for the
activity_logs table. More info below...

In the BioCatalogue we are using an extended version of the
activity_logs mechanism to store all kinds of events. Right now I use
this mainly to calculate how many times a service has been viewed. For
one service with just under 100 view counts this query was taking
between 200 and 300 milliseconds to complete. After adding some indexes
it now takes between 50 to 70 milliseconds.

Pasted after this email are the indexes I added, for reference (note
that the "format" index is specific to the BioCatalogue extensions to
the activity_logs).

Just a thought to improve the performance of the event logging branch,
as I know performance is one of the main concerns that is stopping it
from being released...

Jits

---

class AddIndexesToActivityLogs < ActiveRecord::Migration
  def self.up
    add_index :activity_logs, [ "action" ], :name => "act_logs_action_index"
    add_index :activity_logs, [ "activity_loggable_type",
"activity_loggable_id" ], :name => "act_logs_act_loggable_index"
    add_index :activity_logs, [ "culprit_type", "culprit_id" ], :name =>
"act_logs_culprit_index"
    add_index :activity_logs, [ "referenced_type", "referenced_id" ],
:name => "act_logs_referenced_index"
    add_index :activity_logs, [ "format" ], :name => "act_logs_format_index"
  end

  def self.down
    remove_index :activity_logs, :name => "act_logs_action_index"
    remove_index :activity_logs, :name => "act_logs_act_loggable_index"
    remove_index :activity_logs, :name => "act_logs_culprit_index"
    remove_index :activity_logs, :name => "act_logs_referenced_index"
    remove_index :activity_logs, :name => "act_logs_format_index"
  end
end