Grouper Users - Open Discussion List

[Colin Hudler <>]

Hi,

  I've been testing 1.5 and using the UI as an admin user.  Today I 
browsed as a normal user and noticed that browsing any stem that 
contains a group takes about 5 minutes to display.  Admin user does not 
have this problem.  Displaying, adding, removing group members and 
similar activity is fast, only displaying the groupname when viewing the 
stem is slow.  Any help would be appreciated.

Here's what the mysql slow query logs:

# Query_time: 309  Lock_time: 0  Rows_sent: 1  Rows_examined: 73211277
select distinct group0_.id as id19_, group0_.hibernate_version_number as 
hibernate2_19_, group0_.last_membership_change as last3_19_, 
group0_.parent_stem as parent4_19_, group0_.creator_id as creator5_19_, 
group0_.create_time as create6_19_, group0_.modifier_id as 
modifier7_19_, group0_.modify_time as modify8_19_, group0_.name as 
name19_, group0_.display_name as display10_19_, group0_.extension as 
extension19_, group0_.display_extension as display12_19_, 
group0_.description as descrip13_19_, group0_.context_id as 
context14_19_, group0_.alternate_name as alternate15_19_, 
group0_.type_of_group as type16_19_ from grouper_groups group0_, 
grouper_memberships_all_v membership1_, grouper_memberships_all_v 
membership2_ where membership2_.owner_group_id=group0_.id and 
(membership2_.field_id in ('164eb7d7-4d84-4d6f-85af-f10e0b23fe4a' , 
'c21de599-37fd-4c6e-abe2-ebbf421fcccf' , 
'98a84cc0-1f9f-4cda-baf9-a6fe278addb8' , 
'c36ed358-47de-4b3f-ba7b-a29499465086' , 
'dd723536-0de6-422b-9ed6-cbbd975ec694' , 
'ce569e04-0de7-4743-98fc-ab9d0ea4d3c6')) and (membership2_.member_id in 
('65036954-2acc-4e82-a08e-0a6a0a156c2d' , 
'599eb506-e655-43d9-bbf5-f38988444b3d')) and 
membership2_.immediate_mship_enabled='T' and 
group0_.parent_stem='ff838436-485c-432d-a774-5068734aa111' and 
membership1_.owner_group_id=group0_.id and 
membership1_.field_id='ea756054-31a6-4bba-8d49-8e7219fe40cd' and 
membership1_.member_id='599eb506-e655-43d9-bbf5-f38988444b3d' and 
membership1_.immediate_mship_enabled='T' order by group0_.display_name 
asc limit 50;


Processlist looks something like this when it is running:
 Query   |   38 | Copying to tmp table

Ouch!  I also ran explain on the query

+----+-------------+---------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------+------------------------------+---------+---------------------------------------------------------------+------+----------------------------------------------+
| id | select_type | table   | type   | possible_keys 

                                       | key                          | 
key_len | ref 
| rows | Extra                                        |
+----+-------------+---------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------+------------------------------+---------+---------------------------------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | ms      | range  | 
membership_uniq_idx,membership_member_idx,membership_member_list_idx,fk_membership_field_id,membership_member_cvia_idx,membership_enabled_idx 
| membership_member_cvia_idx   | 130     | NULL 
                                 |  806 | Using where; Using temporary; 
Using filesort |
|  1 | SIMPLE      | gs      | ref    | 
group_set_uniq_idx,group_set_gowner_field_idx,group_set_gowner_member_idx,fk_group_set_field_id,fk_group_set_member_field_id 
                 | fk_group_set_member_field_id | 42      | 
new_grouper.ms.field_id                                       |   18 | 
Using where                                  |
|  1 | SIMPLE      | group0_ | eq_ref | 
PRIMARY,group_parent_idx,group_parent_display_idx 
                                                                      | 
PRIMARY                      | 130     | func 
                               |    1 | Using where 
              |
|  1 | SIMPLE      | gs      | ref    | 
group_set_uniq_idx,group_set_gowner_field_idx,group_set_gowner_member_idx,fk_group_set_field_id,fk_group_set_member_field_id 
                 | group_set_gowner_field_idx   | 85      | 
new_grouper.group0_.id,const                                  |   16 | 
Using where; Distinct                        |
|  1 | SIMPLE      | ms      | eq_ref | 
membership_uniq_idx,membership_member_idx,membership_member_list_idx,fk_membership_field_id,membership_member_cvia_idx,membership_enabled_idx 
| membership_uniq_idx          | 390     | 
new_grouper.gs.member_id,const,new_grouper.gs.member_field_id |    1 | 
Using where; Distinct                        |
+----+-------------+---------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------+------------------------------+---------+---------------------------------------------------------------+------+----------------------------------------------+