It's know issue of mysql, here is reference to a description on request-tracker.ru site http://request-tracker.ru/node/65 (in russian)
On Thu, May 29, 2008 at 1:14 AM, Boris Lytochkin <[EMAIL PROTECTED]> wrote: > > After adding this key: > ALTER TABLE CachedGroupMembers Add KEY `SHREDDER_CGM1` > (`GroupId`,`MemberId`,`Disabled`); > - MemberId & GroupId swapped comparing to original SHREDDER_CGM1 - explain > (and query too :) ) is OK. > > mysql> explain extended SELECT DISTINCT main.* FROM Users main > mysql> CROSS JOIN ACL ACL_4 > mysql> JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) > mysql> JOIN CachedGroupMembers CachedGroupMembers_2 ON ( > CachedGroupMembers_2.MemberId = Principals_1.id ) > mysql> JOIN Groups Groups_3 ON ( Groups_3.id = > CachedGroupMembers_2.GroupId ) > mysql> WHERE (Principals_1.Disabled = '0') AND (ACL_4.PrincipalType = > Groups_3.Type) > mysql> AND (Principals_1.id != '1') AND (Principals_1.PrincipalType = > 'User') > mysql> AND (ACL_4.RightName = 'OwnTicket') AND (Groups_3.Domain = > 'RT::System-Role') > mysql> AND ((ACL_4.ObjectType = 'RT::Ticket' AND ACL_4.ObjectId = 147792) > OR (ACL_4.ObjectType = 'RT::Queue' AND ACL_4.ObjectId = 10) OR > (ACL_4.ObjectType = 'RT::System')) > mysql> ORDER BY main.Name ASC; > +----+-------------+----------------------+--------+--------------------------+---------------+---------+----------------------------------+-------+------------------------------------+ > | id | select_type | table | type | possible_keys > | key | key_len | ref | rows | Extra > | > +----+-------------+----------------------+--------+--------------------------+---------------+---------+----------------------------------+-------+------------------------------------+ > | 1 | SIMPLE | main | ALL | PRIMARY > | NULL | NULL | NULL | 39464 | Using > temporary; Using filesort | > | 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 | 5 | rt3.Principals_1.id | 1 | Using > where; Using index; Distinct | > | 1 | SIMPLE | ACL_4 | range | ACL1 > | ACL1 | 54 | NULL | 5 | Using > where; Using index; Distinct | > | 1 | SIMPLE | Groups_3 | eq_ref | PRIMARY,Groups1,Groups2 > | PRIMARY | 4 | rt3.CachedGroupMembers_2.GroupId | 1 | Using > where; Distinct | > +----+-------------+----------------------+--------+--------------------------+---------------+---------+----------------------------------+-------+------------------------------------+ > > After dropping SHREDDER_CGM1: > +----+-------------+----------------------+--------+-------------------------+------------+---------+-----------------------------------+------+-----------------------------------------------------------+ > | id | select_type | table | type | possible_keys > | key | key_len | ref | rows | Extra > | > +----+-------------+----------------------+--------+-------------------------+------------+---------+-----------------------------------+------+-----------------------------------------------------------+ > | 1 | SIMPLE | Groups_3 | ref | PRIMARY,Groups1,Groups2 > | Groups1 | 65 | const | 4 | Using > where; Using index; Using temporary; Using filesort | > | 1 | SIMPLE | CachedGroupMembers_2 | ref | DisGrouMem > | DisGrouMem | 5 | rt3.Groups_3.id | 1 | Using > where; Using index | > | 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY > | PRIMARY | 4 | rt3.CachedGroupMembers_2.MemberId | 1 | Using > where | > | 1 | SIMPLE | ACL_4 | range | ACL1 > | ACL1 | 54 | NULL | 5 | Using > where; Using index | > | 1 | SIMPLE | main | eq_ref | PRIMARY > | PRIMARY | 4 | rt3.Principals_1.id | 1 | > | > +----+-------------+----------------------+--------+-------------------------+------------+---------+-----------------------------------+------+-----------------------------------------------------------+ > > SHREDDER_CGM1: > mysql> show index from CachedGroupMembers; > +--------------------+------------+---------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+ > | Table | Non_unique | Key_name | Seq_in_index | > Column_name | Collation | Cardinality | Sub_part | Packed | Null | > Index_type | Comment | > +--------------------+------------+---------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+ > ... > | CachedGroupMembers | 1 | SHREDDER_CGM1 | 1 | MemberId > | A | 400337 | NULL | NULL | YES | BTREE | > | > | CachedGroupMembers | 1 | SHREDDER_CGM1 | 2 | GroupId > | A | 400337 | NULL | NULL | YES | BTREE | > | > | CachedGroupMembers | 1 | SHREDDER_CGM1 | 3 | Disabled > | A | 400337 | NULL | NULL | | BTREE | > | > ... > +--------------------+------------+---------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+ > > >> Message: 6 >> Date: Thu, 22 May 2008 16:08:27 +1000 (EST) >> From: David Hobley <[EMAIL PROTECTED]> >> Subject: Re: [rt-users] RT running slowly... Caused by RTx-Shredder >> indexes >> To: rt-users <[email protected]> >> Message-ID: <[EMAIL PROTECTED]> >> Content-Type: text/plain; charset="utf-8" > >> 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 ( > mysql>> 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) > mysql>> 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 > mysql>> ((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 > -- > Best regards, > Boris Lytochkin mailto:[EMAIL PROTECTED] > > _______________________________________________ > 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
