Jeff, always Cc the list. Version of your mysql server?
As far as I can see you suffer from mysql bug, output from your server is equal in both cases what is really wrong and mysql must use new index in those test queries I sent to the list. There are several options: 1) Delete any indexes on CachedGroupMembers table which starts from MemberId column, but that will slowdown other queries and may be terribly, depends on proprotions of your DB. 2) Upgrade to mysql 5.0.45 or greater and create index I suggested in this thread earlier. 3) I have another idea how we can improve that in the code, but that needs more investigation with a lot of users' feedback and a lot of mine and users' time. As long as MySQL 4.x has ended its life time and 5.0.x is stable version then I think it's fair enough to recommend recent versions instead of continuose refactoring of the code to make all those broken mysqls happy. On Wed, Mar 19, 2008 at 6:22 PM, Jeff Voskamp <[EMAIL PROTECTED]> wrote: > > Ruslan Zakirov wrote: > > Ok, I have an idea how to fix that problem > > > > Here is new file for testing that will give me more info to find the > > best way to fixing this. We're really close. > > > > You can run it using: > > mysql -t -u root -ppassword rt3 <../search_possible_owners.mysql.sql > >test.res > > > > As a first step to fix it you can create the following index on Groups > table: > > CREATE INDEX RUZ_Groups1 ON Groups(Domain, Type, id); > > > > Please, run commands from the attachment twice before indexing and after. > > > > Thank you for the feedback. > > > > On Wed, Mar 19, 2008 at 11:49 AM, Richard Ellis <[EMAIL PROTECTED]> wrote: > > > >> Hi Ruslan, > >> > >> Really appreciate the help on this. I'd love to find out why we are > seeing > >> such odd results: > >> > >> 298 ticket owners when their are only 88 active users > >> 1.5 million rows of data when we only have 9983 ticks as of this morning. > >> > >> Really odd > >> > >> Thanks > >> > >> Richard > >> > Since we were also having problems here's our output. > spw.out is before. spw.out2 is after. > > Jeff Voskamp > University of Waterloo > > +----+-------------+----------------------+--------+--------------------------------------------------------------+----------+---------+---------------------------------------+------+----------------------------------------------+ > | id | select_type | table | type | possible_keys > | key | key_len | ref > | rows | Extra | > > +----+-------------+----------------------+--------+--------------------------------------------------------------+----------+---------+---------------------------------------+------+----------------------------------------------+ > | 1 | SIMPLE | main | range | PRIMARY > | PRIMARY | 4 | NULL > | 4138 | Using where; Using temporary; Using filesort | > | 1 | SIMPLE | Groups_3 | ref | > PRIMARY,groups_key,Groups1,Groups2,Groups9,Groups2a,Groups1a | Groups1a | 67 > | const | 630 | Using where; Using > index; Distinct | > | 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY,Principals4 > | PRIMARY | 4 | rt3_inst.main.id > | 1 | Using where; Distinct | > | 1 | SIMPLE | CachedGroupMembers_2 | ref | DisGrouMem,MyCGM1 > | MyCGM1 | 10 | > rt3_inst.main.id,rt3_inst.Groups_3.id | 1 | Using where; Using index; > Distinct | > | 1 | SIMPLE | ACL_4 | range | ACL1 > | ACL1 | 54 | NULL > | 371 | Using where; Using index; Distinct | > > +----+-------------+----------------------+--------+--------------------------------------------------------------+----------+---------+---------------------------------------+------+----------------------------------------------+ > +---------------+-----------+ > | PrincipalType | COUNT(id) | > +---------------+-----------+ > | Cc | 1 | > | Group | 372 | > +---------------+-----------+ > > +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ > | id | select_type | table | type | possible_keys | key | key_len | ref | > rows | Extra | > > +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ > | 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | > 371 | Using where; Using index | > > +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ > +--------------------+ > | COUNT(Groups_3.id) | > +--------------------+ > | 72 | > +--------------------+ > > +----+-------------+----------+-------+-------------------------------------------+---------+---------+-------+------+--------------------------+ > | id | select_type | table | type | possible_keys > | key | key_len | ref | rows | Extra | > > +----+-------------+----------+-------+-------------------------------------------+---------+---------+-------+------+--------------------------+ > | 1 | SIMPLE | ACL_4 | range | ACL1 > | ACL1 | 54 | NULL | 371 | Using where; Using index | > | 1 | SIMPLE | Groups_3 | ref | > Groups1,Groups2,Groups9,Groups2a,Groups1a | Groups1 | 67 | const | 630 > | Using where; Using index | > > +----+-------------+----------+-------+-------------------------------------------+---------+---------+-------+------+--------------------------+ > > +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ > | id | select_type | table | type | possible_keys | key | key_len | ref | > rows | Extra | > > +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ > | 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | > 371 | Using where; Using index | > > +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ > > +----+-------------+----------+-------+-------------------------------------------+---------+---------+-------+------+--------------------------+ > | id | select_type | table | type | possible_keys > | key | key_len | ref | rows | Extra | > > +----+-------------+----------+-------+-------------------------------------------+---------+---------+-------+------+--------------------------+ > | 1 | SIMPLE | ACL_4 | range | ACL1 > | ACL1 | 54 | NULL | 371 | Using where; Using index | > | 1 | SIMPLE | Groups_3 | ref | > Groups1,Groups2,Groups9,Groups2a,Groups1a | Groups1 | 67 | const | 630 > | Using where; Using index | > > +----+-------------+----------+-------+-------------------------------------------+---------+---------+-------+------+--------------------------+ > > +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ > | id | select_type | table | type | possible_keys | key | key_len | ref | > rows | Extra | > > +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ > | 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | > 371 | Using where; Using index | > > +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ > > +----+-------------+----------+-------+-------------------------------------------+---------+---------+-------+------+--------------------------+ > | id | select_type | table | type | possible_keys > | key | key_len | ref | rows | Extra | > > +----+-------------+----------+-------+-------------------------------------------+---------+---------+-------+------+--------------------------+ > | 1 | SIMPLE | ACL_4 | range | ACL1 > | ACL1 | 54 | NULL | 371 | Using where; Using index | > | 1 | SIMPLE | Groups_3 | ref | > Groups1,Groups2,Groups9,Groups2a,Groups1a | Groups1 | 67 | const | 630 > | Using where; Using index | > > +----+-------------+----------+-------+-------------------------------------------+---------+---------+-------+------+--------------------------+ > > +--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name > | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | > > +--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ > | CachedGroupMembers | 0 | PRIMARY | 1 | id > | A | 897772 | NULL | NULL | | BTREE | NULL | > | CachedGroupMembers | 1 | DisGrouMem | 1 | GroupId > | A | 897772 | NULL | NULL | YES | BTREE | NULL | > | CachedGroupMembers | 1 | DisGrouMem | 2 | MemberId > | A | 897772 | NULL | NULL | YES | BTREE | NULL | > | CachedGroupMembers | 1 | DisGrouMem | 3 | Disabled > | A | 897772 | NULL | NULL | | BTREE | NULL | > | CachedGroupMembers | 1 | MyCGM1 | 1 | MemberId > | A | 897772 | NULL | NULL | YES | BTREE | NULL | > | CachedGroupMembers | 1 | MyCGM1 | 2 | GroupId > | A | 897772 | NULL | NULL | YES | BTREE | NULL | > | CachedGroupMembers | 1 | MyCGM1 | 3 | Disabled > | A | 897772 | NULL | NULL | | BTREE | NULL | > > +--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ > > +----+-------------+----------------------+--------+--------------------------------------------------------------------------+----------+---------+---------------------------------------+------+----------------------------------------------+ > | id | select_type | table | type | possible_keys > | key | key_len | ref > | rows | Extra > | > > +----+-------------+----------------------+--------+--------------------------------------------------------------------------+----------+---------+---------------------------------------+------+----------------------------------------------+ > | 1 | SIMPLE | main | range | PRIMARY > | PRIMARY | 4 | NULL > | 4138 | Using where; Using temporary; Using > filesort | > | 1 | SIMPLE | Groups_3 | ref | > PRIMARY,groups_key,Groups1,Groups2,Groups9,Groups2a,Groups1a,RUZ_Groups1 | > Groups1a | 67 | const | 630 | Using > where; Using index; Distinct | > | 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY,Principals4 > | PRIMARY | 4 | > rt3_inst.main.id | 1 | Using where; Distinct > | > | 1 | SIMPLE | CachedGroupMembers_2 | ref | DisGrouMem,MyCGM1 > | MyCGM1 | 10 | > rt3_inst.main.id,rt3_inst.Groups_3.id | 1 | Using where; Using index; > Distinct | > | 1 | SIMPLE | ACL_4 | range | ACL1 > | ACL1 | 54 | NULL > | 371 | Using where; Using index; Distinct > | > > +----+-------------+----------------------+--------+--------------------------------------------------------------------------+----------+---------+---------------------------------------+------+----------------------------------------------+ > +---------------+-----------+ > | PrincipalType | COUNT(id) | > +---------------+-----------+ > | Cc | 1 | > | Group | 372 | > +---------------+-----------+ > > +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ > | id | select_type | table | type | possible_keys | key | key_len | ref | > rows | Extra | > > +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ > | 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | > 371 | Using where; Using index | > > +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ > +--------------------+ > | COUNT(Groups_3.id) | > +--------------------+ > | 72 | > +--------------------+ > > +----+-------------+----------+-------+-------------------------------------------------------+---------+---------+-------+------+--------------------------+ > | id | select_type | table | type | possible_keys > | key | key_len | ref | rows | Extra > | > > +----+-------------+----------+-------+-------------------------------------------------------+---------+---------+-------+------+--------------------------+ > | 1 | SIMPLE | ACL_4 | range | ACL1 > | ACL1 | 54 | NULL | 371 | Using where; Using > index | > | 1 | SIMPLE | Groups_3 | ref | > Groups1,Groups2,Groups9,Groups2a,Groups1a,RUZ_Groups1 | Groups1 | 67 | > const | 630 | Using where; Using index | > > +----+-------------+----------+-------+-------------------------------------------------------+---------+---------+-------+------+--------------------------+ > > +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ > | id | select_type | table | type | possible_keys | key | key_len | ref | > rows | Extra | > > +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ > | 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | > 371 | Using where; Using index | > > +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ > > +----+-------------+----------+-------+-------------------------------------------------------+---------+---------+-------+------+--------------------------+ > | id | select_type | table | type | possible_keys > | key | key_len | ref | rows | Extra > | > > +----+-------------+----------+-------+-------------------------------------------------------+---------+---------+-------+------+--------------------------+ > | 1 | SIMPLE | ACL_4 | range | ACL1 > | ACL1 | 54 | NULL | 371 | Using where; Using > index | > | 1 | SIMPLE | Groups_3 | ref | > Groups1,Groups2,Groups9,Groups2a,Groups1a,RUZ_Groups1 | Groups1 | 67 | > const | 630 | Using where; Using index | > > +----+-------------+----------+-------+-------------------------------------------------------+---------+---------+-------+------+--------------------------+ > > +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ > | id | select_type | table | type | possible_keys | key | key_len | ref | > rows | Extra | > > +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ > | 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | > 371 | Using where; Using index | > > +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ > > +----+-------------+----------+-------+-------------------------------------------------------+---------+---------+-------+------+--------------------------+ > | id | select_type | table | type | possible_keys > | key | key_len | ref | rows | Extra > | > > +----+-------------+----------+-------+-------------------------------------------------------+---------+---------+-------+------+--------------------------+ > | 1 | SIMPLE | ACL_4 | range | ACL1 > | ACL1 | 54 | NULL | 371 | Using where; Using > index | > | 1 | SIMPLE | Groups_3 | ref | > Groups1,Groups2,Groups9,Groups2a,Groups1a,RUZ_Groups1 | Groups1 | 67 | > const | 630 | Using where; Using index | > > +----+-------------+----------+-------+-------------------------------------------------------+---------+---------+-------+------+--------------------------+ > > +--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name > | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | > > +--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ > | CachedGroupMembers | 0 | PRIMARY | 1 | id > | A | 901467 | NULL | NULL | | BTREE | NULL | > | CachedGroupMembers | 1 | DisGrouMem | 1 | GroupId > | A | 901467 | NULL | NULL | YES | BTREE | NULL | > | CachedGroupMembers | 1 | DisGrouMem | 2 | MemberId > | A | 901467 | NULL | NULL | YES | BTREE | NULL | > | CachedGroupMembers | 1 | DisGrouMem | 3 | Disabled > | A | 901467 | NULL | NULL | | BTREE | NULL | > | CachedGroupMembers | 1 | MyCGM1 | 1 | MemberId > | A | 901467 | NULL | NULL | YES | BTREE | NULL | > | CachedGroupMembers | 1 | MyCGM1 | 2 | GroupId > | A | 901467 | NULL | NULL | YES | BTREE | NULL | > | CachedGroupMembers | 1 | MyCGM1 | 3 | Disabled > | A | 901467 | NULL | NULL | | BTREE | NULL | > > +--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ > > -- 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
