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 <[email protected]> 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
