Known issue with mysql's optimizer. Either drop index or upgrade mysql to 5.0.45 and newer. Mysql 5.1.x have some optmiser issues as well.
On Thu, May 22, 2008 at 10:08 AM, David Hobley <[EMAIL PROTECTED]> wrote: > All, > > I ran an explain on the query in MySQL and noticed that it was using one of > the RTx-Shredder indexes. After I deleted all those indexes things went back > to normal. Phew. > > In case this is useful: > > mysql> explain select distinct main.* from Users main cross join ACL ACL_4 > JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN > CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId > = Principals_1.id ) JOIN Groups Groups_3 ON ( Groups_3.id = > CachedGroupMembers_2.GroupId ) WHERE (Principals_1.Disabled = '0') AND > (ACL_4.PrincipalType = Groups_3.Type) AND (Principals_1.id != '1') AND > (Principals_1.PrincipalType = 'User') AND (ACL_4.RightName = 'OwnTicket') > 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.Name ASC; > +----+-------------+----------------------+--------+--------------------------+---------------+---------+-----------------------------+-------+----------------------------------------------+ > | id | select_type | table | type | > possible_keys | key | key_len | > ref | rows | > Extra | > +----+-------------+----------------------+--------+--------------------------+---------------+---------+-----------------------------+-------+----------------------------------------------+ > | 1 | SIMPLE | main | range | > PRIMARY | PRIMARY | 4 | > NULL | 28920 | Using where; Using temporary; Using > filesort | > | 1 | SIMPLE | Groups_3 | range | > PRIMARY,Groups1,Groups2 | Groups1 | 67 | > NULL | 119 | Using where; Using index; > Distinct | > | 1 | SIMPLE | Principals_1 | eq_ref | > PRIMARY | PRIMARY | 4 | > rt3.main.id | 1 | Using where; > Distinct | > | 1 | SIMPLE | CachedGroupMembers_2 | ref | > DisGrouMem,SHREDDER_CGM1 | SHREDDER_CGM1 | 10 | > rt3.main.id,rt3.Groups_3.id | 1 | Using where; Using index; > Distinct | > | 1 | SIMPLE | ACL_4 | range | > ACL1 | ACL1 | 54 | > NULL | 45 | Using where; Using index; > Distinct | > +----+-------------+----------------------+--------+--------------------------+---------------+---------+-----------------------------+-------+----------------------------------------------+ > > Cheers, > David > ----- Original Message ----- > From: "David Hobley" <[EMAIL PROTECTED]> > To: "rt-users" <[email protected]> > Sent: Thursday, 22 May, 2008 3:30:21 PM GMT +10:00 Brisbane > Subject: [rt-users] RT running slowly... > > Hello, > > Our RT has been running slowly for a while; having turned on mysql slow > query logging, it appears to only be a single query which is causing grief. > Anyone any suggestions as to what can be done to fix this? > > # Time: 080522 15:32:21 > # [EMAIL PROTECTED]: rt[rt] @ localhost [] > # Query_time: 40 Lock_time: 0 Rows_sent: 0 Rows_examined: 6888927 > SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_4 JOIN Principals > Principals_1 ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers > CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) > JOIN Groups Groups_3 ON ( Groups_3.id = CachedGroupMembers_2.GroupId ) > WHERE (Principals_1.Disabled = '0') AND (ACL_4.PrincipalType = > Groups_3.Type) AND (Principals_1.id != '1') AND (Principals_1.PrincipalType > = 'User') AND (ACL_4.RightName = 'OwnTicket') 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.Name ASC; > > -- > Cheers, > David > > _______________________________________________ > http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community > help: http://wiki.bestpractical.com Commercial support: > [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from > O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com > > -- > Cheers, > David Hobley > > IT Manager > Creators of Miessence, MiVitality and MiEnviron > > Phone: +61 (7) 5582 7020 > Fax: +61 (7) 5539 6719 > USA Fax 1800 840 0827 > Email : [EMAIL PROTECTED] > Website: www.mionegroup.com > > > > _______________________________________________ > http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users > > Community help: http://wiki.bestpractical.com > Commercial support: [EMAIL PROTECTED] > > > Discover RT's hidden secrets with RT Essentials from O'Reilly Media. > Buy a copy at http://rtbook.bestpractical.com > -- Best regards, Ruslan. _______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
