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

[Ruslan Zakirov]

as far as I can see estimation of "rows" is very wrong I do think it's
mysql bug, I suggest you rebuild all indexes and run analyze on
tables.

On 4/26/07, Jesse Vincent <jesse at bestpractical.com> wrote:
> That looks suspiciously like the problem I'd been talking to you about
> before, Todd.
>
>
> On Apr 25, 2007, at 11:27 PM, Philip Kime wrote:
>
>
> I know I asked this before but I've been swamped and lost track of there the
> discussion got to.
>
> RT 3.6.3, Mysql 5.0.27. The main ticket search page is really slow to load,
> typically 45 seconds, sometimes longer. Problem query and explain below.
> It's starting to become a problem for us. Seems that the first row of the
> explain output is the guilty one. Rows_examined is absurdly high.
>
> PK
>
> # Query_time: 45  Lock_time: 0  Rows_sent: 290  Rows_examined: 65256162
> SELECT DISTINCT main.* FROM Users main , Principals Principals_1,
> CachedGroupMembers CachedGroupMembers_2, Groups Groups_3, ACL ACL_4  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;
>
> *************************** 1. row ***************************
>            id: 1
>   select_type: SIMPLE
>         table: main
>          type: range
> possible_keys: PRIMARY
>           key: PRIMARY
>       key_len: 4
>           ref: NULL
>          rows: 673
>         Extra: Using where; Using temporary; Using filesort
> *************************** 2. row ***************************
> *************************** 2. row ***************************
>            id: 1
>   select_type: SIMPLE
>         table: Principals_1
>          type: eq_ref
> possible_keys: PRIMARY
>           key: PRIMARY
>       key_len: 4
>           ref: rt3.main.id
>          rows: 1
>         Extra: Using where; Distinct
> *************************** 3. row ***************************
>            id: 1
>   select_type: SIMPLE
>         table: CachedGroupMembers_2
>          type: ref
> possible_keys: DisGrouMem,SHRD_CGM1
>           key: SHRD_CGM1
>       key_len: 5
>           ref: rt3.main.id
>          rows: 1
>         Extra: Using where; Using index; Distinct
> *************************** 4. 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; Distinct
> *************************** 5. row ***************************
>            id: 1
>   select_type: SIMPLE
>         table: Groups_3
>          type: eq_ref
> possible_keys: PRIMARY,Groups1,Groups2
>           key: PRIMARY
>       key_len: 4
>           ref: rt3.CachedGroupMembers_2.GroupId
>          rows: 1
>         Extra: Using where; Distinct
>
>
>
> --
> Philip Kime
> NOPS Systems Architect
> 310 401 0407
>
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com
>
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com
>
>


-- 
Best regards, Ruslan.