Grouper Developers Forum

["GW Brown, Information Systems and Computing" <>]

My take on the complete list below, first some general comments:

1) In an ideal world we would have a comprehensive benchmark script vs a 
known large database so that we can see what difference, if any, dropping / 
adding indexes makes. We probably can't assume that all databases use 
indexes in the same way.
2) Can we make some of the indexes optional through a config file? Just 
because we don't use some filters much at the moment doesn't mean others 
might choose to use them. Create times, modified by could be useful at 
reducing result sets but we haven't, in the UI, provided the means to 
exercise such filters


> Here is an index review:
>
> grouper_attributes:
>   - attribute_field_value_idx (field_id, value): needed to find all
> grouper with a certain attribute (Shilen mentioned)
>   - attribute_group_idx (group_id): needed to find all attributes for a
> group
>   - attribute_uniq_idx (group_id, field_id [unique]): each group can have
> only one of an attribute
>   - attribute_value_idx (value): does someone have a use case for this???
> drop?
GroupAnyAttributeFilter calls
Hib3GroupDAO.findAllByAnyApproximateAttr which searches just the value 
field but does a substring search so the index wouldn't be used. I can 
imagine wanting to do an exact match, but in most cases you would do that 
vs a field_id, so it is probably safe to drop this index
>   SUGGESTIONS:
>     - drop attribute_group_idx since redundant with attribute_uniq_idx
That makes sense unless at some stage we allow multi-valued group 
attributes - but I don't think that is on the cards at the moment and we 
can always update the indexes as and when we need to, so dropping it should 
be OK
> grouper_composites:
>   - composite_composite_idx (owner): find composites based on owner
>   - composite_createtime_idx (create_time):
>   - composite_creator_idx (creator)
>   - composite_factor_idx (left_factor, right_factor)
>   - composite_left_factor (left_factor)
>   - composite_right_factor (right_factor)
>
>   SUGGESTIONS:
>     - drop composite_createtime_idx
>     - drop composite_creator_idx
>     - drop composite_left_factor (since redundant with
> composite_factor.idx)
We don't have any filters for composites so should be safe to remove those 
indexes
> grouper_ddl:
>   - grouper_ddl_object_name_idx [unique]: keeps ddl object name unique
>
> grouper_fields:
>   - name_type (name, type [unique]): needed to keep these two unique, and
> maybe for performance
>
> grouper_groups:
>   - group_createtime_idx (create_time)
>   - group_creator_idx (creator_id)
>   - group_modifier_idx (modifier_id)
>   - group_modify_time_idx
>   - group_parent_idx (parent_stem): find all groups under a parent
>
>   SUGGESTIONS:
>     - drop: group_createtime_idx
I would keep
>     - drop: group_creator_idx
>     - drop: group_modifier_idx
I'd probably want these to be at least optional
> grouper_groups_types:
>     - grouptypetuple_group_idx (group_uuid)
>     - grouptypetuple_type_idx (type_uuid)
>     - grouptypetuple_grouptype_idx [unique] (group_uuid, type_uuid)
>
>     SUGGESTIONS:
>       - drop grouptypetuple_group_idx (redundant with
> grouptypetuple_grouptype_idx)
OK
> grouper_loader_log:
>   - grouper_loader_job_name (job_name): easily find logs for a job
>
> grouper_members:
>   - member_subjectid_idx: subject_id
>   - member_subjectsource_idx: subject_source (find all subjects in a
> certain source)
>   - member_subjectsourceType_idx [unique]: subject_id, subject_source,
> subject_type
>   - member_subjecttype_idx: subject_type
>
>     SUGGESTIONS:
>       - drop member_subjectid_idx since redundant with
> member_subjectsourcetype_idx
>       - drop member_subjecttype_idx since type is going away
May be wait until it does go away, though I'm not sure if anything uses it.
> grouper_memberships:
>   - membership_createtime_idx (create_time)
>   - membership_creator_idx (creator_id)
>   - membership_depth_idx (depth): use case for this???
>   - membership_member_idx (member_id)
>   - membership_member_list_idx (member_id, field_id)
>   - membership_member_via_idx (member_id, via_id)
>   - membership_owner_field_type_idx (owner_id, field_id, mship_type)
>   - membership_owner_idx (owner_id)
>   - membership_owner_member_idx (owner_id, member_id, field_id, depth
>   - membership_parent_idx (parent_membership)
>   - membership_type_idx (mship_type): anyone know a use case for this one?
>   - membership_via_idx (via_id)
>
>   SUGGESTIONS:
>     - drop membership_creator_idx
>     - drop membership_depth_idx
>     - drop membership_member_idx (redundant with
> membership_member_list_idx
>     - drop membership_owner_idx (since redundant with other indexes)
OK
>     - membership_type_idx (anyone know a use case with this? drop it)
Don't drop it! There are methods, used in the UI, which let you select 
immediate or effective memberships e.g. 
Hib3MembershipDAO.findAllImmediateByMember
> grouper_stems
>   - stem_createtime_idx (create_time)
>   - stem_creator_idx (creator_id)
>   - stem_displayextn_idx (display_extension)
>   - stem_displayname_idx (display_name)
>   - stem_extn_idx (extension)
>   - stem_modifier_idx (modifier_id)
>   - stem_modifytime_idx (modify_time)
>   - stem_name_idx (name)
>   - stem_parent_idx (parent_stem)
>
>   SUGGESTIONS:
>     - drop stem_createtime_idx
>     - drop stem_creator_idx
>     - drop stem_modifier_idx
I would want them to be optional
>     - do we ever query stems by display extenstion or name? drop?
We don't - or at least not exact or startsWith type queries which would 
benefit from an index
> grouper_types
>   - grouper_types (name) [unique]

Gary
--On 02 September 2008 11:49 -0500 Tom Barton 
<>
 wrote:

> Emily Eisbruch wrote:
> > [A1] {Group} will review and provide thoughts on GRP 146.
> > https://bugs.internet2.edu/jira/browse/GRP-146
>
> Regarding the suggestions for the grouper_groups table
>
>      - drop: group_createtime_idx
>      - drop: group_creator_idx
>      - drop: group_modifier_idx
>      - drop: group_modify_time_idx
>
> do we need to maintain group_createtime_idx and/or group_modify_time_idx
> to support corresponding GrouperQuery filters? Similar question for
> grouper_stems.
>
> Tom



----------------------
GW Brown, Information Systems and Computing