[rt-users] Is this slow? It seems slow.

[Jamie Wilkinson]

This one time, at band camp, Jamie Wilkinson wrote:
>This one time, at band camp, Jesse Vincent wrote:
>>
>>
>>
>>On Wed, Jun 11, 2003 at 11:33:52AM +1000, Jamie Wilkinson wrote:
>>> This one time, at band camp, Val Luck wrote:
>>> >As it is right now with testing (creating tickets, re-assigning them, resolving them, etc) I can get up to a 5 to 15 second delay when going from "display" to "jumbo" mode.  
>>> 
>>> And similarly when clicking on Update from the front page, or
>>> Resolve/Comment/Reply on the Display page.  Yes, this behaviour has been
>>> seen in the past.  version 3.0.3pre2 of RT with a recent version of
>>> DBIx::SearchBuilder (now 0.86) has fixed the speed problem for some people.
>>> Unfortunately, I am not one of them.  My experience - 3.0.0 and 3.0.1 ran
>>> dog slow, 3.0.1 with a 0.83_01 of DBIx::SearchBuilder ran faster, but page
>>> loads were still taking about 30 seconds for the above operations, and now
>>> with 3.0.3pre2 and DBIx::SearchBuilder 0.86 I am again experiencing 20
>>> minute database queries.
>>
>>That seems Surprising. More details would be appreciated.
>
>Attached is the query from DBIx::Searchbuilder 0.86, the query plan from an
>explain on the query, and an explain analyze (which is probably more useful,
>and shows the total running time of the query).
>
>I'm trying to see a way in which the query has differed from earlier
>versions, but apart from the extra parentheses, it looks familiar.
>
>What does the CachedGroupMembers table do, and what do each of the columns
>mean?

Is cachedgroupmembers supposed to be a mapping between member ids of user
principals and the group ids of a groups?

If so, are the two CachedGroupMembers JOINs supposed to be in fact making
two separate queries:

 * one to return all the groups whose groupid (in this case) is 4,

 and

 * one to return all the groups that are user defined, system internal, or
   ACLEquivalent?

If that's true, could it be that a UNION query would be much better,
allowing the database to perform each of these queries separately and fast,
and then composing the results together?

-- 
jaq at spacepants.org                           http://spacepants.org/jaq.gpg