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

[Jiten Bhagat]

Oops, it's actually changed from 200-300ms to 5-7ms (!) Note that this
is just the query time, and with an activity_logs table with 132K+ records.

Jits



Jiten Bhagat wrote:
> 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
>
>