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

[Danius Michaelides]

On Wed, 7 Oct 2009, 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...

I added indexes to the myexp event_logging branch awhile ago:

add_index :activity_logs, [ "activity_loggable_type", "activity_loggable_id" ] 
...
add_index :activity_logs, [ "referenced_type", "referenced_id" ] ...
add_index :activity_logs, [ "culprit_type", "culprit_id" ] ...

No index for "action" - i guess coz i couldnt see any queries using it.

But yeah, big improvement, just be careful not to make indexes that dont
get used, as there's obviously a performance hit when inserting.
Danius