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:

- 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
- 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???
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

- 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

- 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)

- drop composite_createtime_idx
- drop composite_creator_idx
- drop composite_left_factor (since redundant with
We don't have any filters for composites so should be safe to remove those indexes

- grouper_ddl_object_name_idx [unique]: keeps ddl object name unique

- name_type (name, type [unique]): needed to keep these two unique, and
maybe for performance

- 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

- 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

- grouptypetuple_group_idx (group_uuid)
- grouptypetuple_type_idx (type_uuid)
- grouptypetuple_grouptype_idx [unique] (group_uuid, type_uuid)

- drop grouptypetuple_group_idx (redundant with

- grouper_loader_job_name (job_name): easily find logs for a job

- 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,
- member_subjecttype_idx: subject_type

- drop member_subjectid_idx since redundant with
- 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.

- 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)

- drop membership_creator_idx
- drop membership_depth_idx
- drop membership_member_idx (redundant with
- drop membership_owner_idx (since redundant with other indexes)
- 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

- 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)

- 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 (name) [unique]

--On 02 September 2008 11:49 -0500 Tom Barton

Emily Eisbruch wrote:
[A1] {Group} will review and provide thoughts on 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


GW Brown, Information Systems and Computing

Archive powered by MHonArc 2.6.16.

Top of Page