Skip to Content.
Sympa Menu

grouper-dev - Re: [grouper-dev] Re: GRP-146

Subject: Grouper Developers Forum

List archive

Re: [grouper-dev] Re: GRP-146


Chronological Thread 
  • From: "GW Brown, Information Systems and Computing" <>
  • To: Tom Barton <>,
  • Subject: Re: [grouper-dev] Re: GRP-146
  • Date: Wed, 03 Sep 2008 09:59:27 +0100

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




Archive powered by MHonArc 2.6.16.

Top of Page