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

[Jiten Bhagat]

Ah, great! Thanks Dan.

Jits


Danius Michaelides wrote:
> 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
>
>
> _______________________________________________
> myexperiment-hackers mailing list
> address@hidden
> http://lists.nongnu.org/mailman/listinfo/myexperiment-hackers