[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [myexperiment-hackers] Adding indexes to the activity_logs table to
Re: [myexperiment-hackers] Adding indexes to the activity_logs table to improve query performances
Wed, 07 Oct 2009 13:59:16 +0100
Thunderbird 184.108.40.206 (Windows/20090812)
Ah, great! Thanks Dan.
Danius Michaelides wrote:
> On Wed, 7 Oct 2009, Jiten Bhagat wrote:
>> 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.
> myexperiment-hackers mailing list