[rt-users] TicketHistory slow on 4.2.0

[Tod Detre]

Adding the index did indeed fix the issue. I've pasted the line from
the SQL log below. It looks like the query is coming from
RT::Transaction. I'm not that great at perl so I'm not sure, but I
think it might be the method LoadCustomFieldByIdentifier. I do know
that the URL that starts the query is /Helpers/TicketHistory.

[29826] [Mon Oct 28 13:33:12 2013] [debug]: SQL(2.188469s): SELECT
count(main.id) FROM ObjectCustomFieldValues main  WHERE (main.Disabled
= '0') AND (main.ObjectId = '307581') AND (main.ObjectType =
'RT::Transaction') ; (/opt/rt4/sbin/../lib/RT/Interface/Web.pm:1292)

On Fri, Oct 25, 2013 at 5:22 PM, Alex Vandiver <alexmv at bestpractical.com> wrote:
> On Fri, 2013-10-25 at 16:56 -0400, Tod Detre wrote:
>> Here are the EXPLAIN results: [snip]
>>
>> So it looks like the first query is using an index, but the second is
>> not. I've confirmed that the ObjectCustomFieldValues table has the
>> correct indexes listed in the schema.mysql. However the disabled field
>> is not indexed. Should that be added?
>
> The difficulty is that there is no index on ObjectType, ObjectId;
> ObjectCustomFieldValues2 starts with the CustomField column, which is
> not limited here, so it cannot be used.
>
> Try adding the following index:
>
> CREATE INDEX ObjectCustomFieldValues_Object
>     ON ObjectCustomFieldValues(ObjectType, ObjectId, Disabled);
>
> I additionally want to know what is generating that query.  Turning on
> http://docs.bestpractical.com/RT_Config#StatementLog (on a
> single-process development instance) will allow you to find which
> component is generating the query, and if thus it is likely to be a call
> path which others will encounter this problem with.  This will effect if
> and how we fix this for others in 4.2.1
>  - Alex
>



-- 
Tod