[rt-users] Slow ticket search page becoming a problem

[Philip Kime]

You're clearly a better DBA than me :-) Yes, that Join orering is very
nice and executes in about 0.8 seconds as opposed to 45-90 seconds.
Explain is:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ACL_4
         type: range
possible_keys: ACL1
          key: ACL1
      key_len: 54
          ref: NULL
         rows: 77
        Extra: Using where; Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: Groups_3
         type: ref
possible_keys: PRIMARY,Groups1,Groups2
          key: Groups2
      key_len: 67
          ref: rt3.ACL_4.PrincipalType
         rows: 26460
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: CachedGroupMembers_2
         type: ref
possible_keys: DisGrouMem,SHRD_CGM1
          key: DisGrouMem
      key_len: 5
          ref: rt3.Groups_3.id
         rows: 1
        Extra: Using where; Using index
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: Principals_1
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: rt3.CachedGroupMembers_2.MemberId
         rows: 1
        Extra: Using where
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: main
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: rt3.CachedGroupMembers_2.MemberId
         rows: 1
        Extra:
5 rows in set (0.00 sec)

PK

-----Original Message-----
From: Ruslan Zakirov [mailto:ruslan.zakirov at gmail.com] 
Sent: Thursday, April 26, 2007 5:34 PM
To: Philip Kime
Cc: Jesse Vincent; Todd Chapman; RT Users
Subject: Re: [rt-users] Slow ticket search page becoming a problem

Philip, please try the following query and send us times and EXPLAIN:

SELECT STRAIGHT_JOIN DISTINCT main.* FROM
    ACL ACL_4,
    Groups Groups_3,
    CachedGroupMembers CachedGroupMembers_2,
    Principals Principals_1,
    Users main
WHERE ((ACL_4.PrincipalType = Groups_3.Type))
    AND ((ACL_4.RightName = 'OwnTicket'))
    AND ((CachedGroupMembers_2.MemberId = Principals_1.id))
    AND ((Groups_3.id = CachedGroupMembers_2.GroupId))
    AND ((Principals_1.Disabled = '0'))
    AND ((Principals_1.PrincipalType = 'User'))
    AND ((Principals_1.id != '1'))
    AND ((main.id = Principals_1.id))
    AND ((ACL_4.ObjectType = 'RT::Queue') OR (ACL_4.ObjectType =
'RT::System'))
    AND ((Groups_3.Domain = 'RT::Queue-Role') OR (Groups_3.Domain =
'RT::System-Role'))
ORDER BY main.RealName ASC;

It's the same query but with forced order of joins, I do believe that
this is the ideal plan for joins in this situation for all setups.


On 4/27/07, Philip Kime <pkime at shopzilla.com> wrote:
>
>
> Ok, the issue is that MYSQL 5 won't use the index on main.Name by 
> default (possible keys list PRIMARY only, which is useless for this 
> ORDER BY clause), which it really needs to do with an ORDER BY clause 
> for main.Name (or main.RealName as in my example as I have modified 
> the display code). It is fixed if you force the index use:
>
[snip]

>
> Then it's nice and fast again. The explain shows that it's still a 
> filesort/temp query but it does a indexed table scan instead of an 
> unindexed range scan.
>
> I assume that this would need a SearchBuilder mod to force the use of 
> the index related to the ORDER BY clause?
>
> PK

--
Best regards, Ruslan.