On Wed, Mar 19, 2008 at 04:22:46PM +0000, Richard Ellis wrote:
> Hi Ruslan,
>
> here's the two sets of results.
FWIW, from your response to ruslan, it _does_ look like your hand-added
"group1" index was messing up the query planner. It's on GroupId, while
we already had an index on GroupId, MemberId.
>
> Thanks
>
> Richard
>
>
> 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
> >>
> >>
> >>
> >
> >[snip]
> >
> >
> >
> +----+-------------+----------------------+--------+-----------------------------------+---------+---------+----------------------------------+------+----------------------------------------------+
> | id | select_type | table | type | possible_keys
> | key | key_len | ref | rows |
> Extra |
> +----+-------------+----------------------+--------+-----------------------------------+---------+---------+----------------------------------+------+----------------------------------------------+
> | 1 | SIMPLE | main | range | PRIMARY,Users3
> | PRIMARY | 4 | NULL | 1317 |
> Using where; 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,GrouMem,group1,member1 | member1 | 5 | rt3.Principals_1.id
> | 1 | Using where; Distinct |
> | 1 | SIMPLE | ACL_4 | range | ACL1
> | ACL1 | 54 | NULL | 296 |
> Using where; Using index; Distinct |
> | 1 | SIMPLE | Groups_3 | eq_ref | PRIMARY,Groups1,Groups2
> | PRIMARY | 4 | rt3.CachedGroupMembers_2.GroupId | 1 |
> Using where; Distinct |
> +----+-------------+----------------------+--------+-----------------------------------+---------+---------+----------------------------------+------+----------------------------------------------+
> +---------------+-----------+
> | PrincipalType | COUNT(id) |
> +---------------+-----------+
> | Group | 298 |
> +---------------+-----------+
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> | id | select_type | table | type | possible_keys | key | key_len | ref |
> rows | Extra |
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> | 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL |
> 296 | Using where; Using index |
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> +--------------------+
> | COUNT(Groups_3.id) |
> +--------------------+
> | 0 |
> +--------------------+
> +----+-------------+----------+-------+-----------------+---------+---------+-------------------------+------+--------------------------+
> | id | select_type | table | type | possible_keys | key | key_len |
> ref | rows | Extra |
> +----+-------------+----------+-------+-----------------+---------+---------+-------------------------+------+--------------------------+
> | 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 |
> NULL | 296 | Using where; Using index |
> | 1 | SIMPLE | Groups_3 | ref | Groups1,Groups2 | Groups2 | 67 |
> rt3.ACL_4.PrincipalType | 1345 | 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 |
> 296 | 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 | 296 | Using where; Using index |
> | 1 | SIMPLE | Groups_3 | ref | Groups1,Groups2 | Groups2 | 67 |
> rt3.ACL_4.PrincipalType | 1345 | 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 |
> 296 | 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 | 296 | Using where; Using index |
> | 1 | SIMPLE | Groups_3 | ref | Groups1,Groups2 | Groups2 | 67 |
> rt3.ACL_4.PrincipalType | 1345 | 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 | 99912 | NULL | NULL | | BTREE | |
> | CachedGroupMembers | 1 | DisGrouMem | 1 | GroupId |
> A | 99912 | NULL | NULL | YES | BTREE | |
> | CachedGroupMembers | 1 | DisGrouMem | 2 | MemberId |
> A | 99912 | NULL | NULL | YES | BTREE | |
> | CachedGroupMembers | 1 | DisGrouMem | 3 | Disabled |
> A | 99912 | NULL | NULL | | BTREE | |
> | CachedGroupMembers | 1 | GrouMem | 1 | GroupId |
> A | 99912 | NULL | NULL | YES | BTREE | |
> | CachedGroupMembers | 1 | GrouMem | 2 | MemberId |
> A | 99912 | NULL | NULL | YES | BTREE | |
> | CachedGroupMembers | 1 | group1 | 1 | GroupId |
> A | 99912 | NULL | NULL | YES | BTREE | |
> | CachedGroupMembers | 1 | member1 | 1 | MemberId |
> A | 99912 | NULL | NULL | YES | BTREE | |
> +--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> +----+-------------+----------------------+--------+-------------------------------------+-------------+---------+-----------------------------------+------+-----------------------------------------------------------+
> | id | select_type | table | type | possible_keys
> | key | key_len | ref | rows
> | Extra |
> +----+-------------+----------------------+--------+-------------------------------------+-------------+---------+-----------------------------------+------+-----------------------------------------------------------+
> | 1 | SIMPLE | ACL_4 | range | ACL1
> | ACL1 | 54 | NULL | 296
> | Using where; Using index; Using temporary; Using filesort |
> | 1 | SIMPLE | Groups_3 | ref |
> PRIMARY,Groups1,Groups2,RUZ_Groups1 | RUZ_Groups1 | 134 |
> const,rt3.ACL_4.PrincipalType | 365 | Using where; Using index
> |
> | 1 | SIMPLE | CachedGroupMembers_2 | ref |
> DisGrouMem,GrouMem,group1,member1 | 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 | main | eq_ref | PRIMARY,Users3
> | PRIMARY | 4 | rt3.Principals_1.id | 1
> | Using where |
> +----+-------------+----------------------+--------+-------------------------------------+-------------+---------+-----------------------------------+------+-----------------------------------------------------------+
> +---------------+-----------+
> | PrincipalType | COUNT(id) |
> +---------------+-----------+
> | Group | 298 |
> +---------------+-----------+
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> | id | select_type | table | type | possible_keys | key | key_len | ref |
> rows | Extra |
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> | 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL |
> 296 | Using where; Using index |
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> +--------------------+
> | COUNT(Groups_3.id) |
> +--------------------+
> | 0 |
> +--------------------+
> +----+-------------+----------+-------+-----------------------------+-------------+---------+-------------------------------+------+--------------------------+
> | id | select_type | table | type | possible_keys | key
> | key_len | ref | rows | Extra
> |
> +----+-------------+----------+-------+-----------------------------+-------------+---------+-------------------------------+------+--------------------------+
> | 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1
> | 54 | NULL | 296 | Using where; Using
> index |
> | 1 | SIMPLE | Groups_3 | ref | Groups1,Groups2,RUZ_Groups1 |
> RUZ_Groups1 | 134 | const,rt3.ACL_4.PrincipalType | 365 | 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 |
> 296 | 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 | 296 | Using where; Using
> index |
> | 1 | SIMPLE | Groups_3 | ref | Groups1,Groups2,RUZ_Groups1 |
> RUZ_Groups1 | 134 | const,rt3.ACL_4.PrincipalType | 365 | 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 |
> 296 | 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 | 296 | Using where; Using
> index |
> | 1 | SIMPLE | Groups_3 | ref | Groups1,Groups2,RUZ_Groups1 |
> RUZ_Groups1 | 134 | const,rt3.ACL_4.PrincipalType | 365 | 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 | 97966 | NULL | NULL | | BTREE | |
> | CachedGroupMembers | 1 | DisGrouMem | 1 | GroupId |
> A | 97966 | NULL | NULL | YES | BTREE | |
> | CachedGroupMembers | 1 | DisGrouMem | 2 | MemberId |
> A | 97966 | NULL | NULL | YES | BTREE | |
> | CachedGroupMembers | 1 | DisGrouMem | 3 | Disabled |
> A | 97966 | NULL | NULL | | BTREE | |
> | CachedGroupMembers | 1 | GrouMem | 1 | GroupId |
> A | 97966 | NULL | NULL | YES | BTREE | |
> | CachedGroupMembers | 1 | GrouMem | 2 | MemberId |
> A | 97966 | NULL | NULL | YES | BTREE | |
> | CachedGroupMembers | 1 | group1 | 1 | GroupId |
> A | 97966 | NULL | NULL | YES | BTREE | |
> | CachedGroupMembers | 1 | member1 | 1 | MemberId |
> A | 97966 | NULL | NULL | YES | BTREE | |
> +--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> _______________________________________________
> 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
--
_______________________________________________
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