[GRP-10] Modify Membership Indices Created: 23/Jul/07  Updated: 17/Nov/07  Resolved: 09/Aug/07

Status: Resolved
Project: Grouper
Component/s: API
Affects Version/s: 1.2.0
Fix Version/s: 1.2.1

Type: Improvement Priority: Major
Reporter: Blair Christensen Assignee: Blair Christensen
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified


 Description   

Shilen Patel @ Duke:

"So we tried the following.

drop index MEMBERSHIP_FIELD_IDX;
drop index MEMBERSHIP_OWNER_IDX;
drop index MEMBERSHIP_TYPE_IDX;

CREATE INDEX IMS.GROUPER_MEMBERSHIPS_IDX1 ON IMS.GROUPER_MEMBERSHIPS
(OWNER_ID, LIST_NAME, LIST_TYPE, MSHIP_TYPE);
CREATE INDEX IMS.GROUPER_MEMBERSHIPS_IDX2 ON IMS.GROUPER_MEMBERSHIPS
(MEMBER_ID, LIST_NAME, LIST_TYPE);

I've tried these index changes on two database servers and have seen
similar results on both.

The time to delete a group drops from 2.18 seconds to .18 seconds.
The time to delete a stem drops from .40 seconds to .08 seconds.
The time to run Member.getMemberships() for a person with 7 memberships
drops from .55 seconds to .10 seconds."

 Comments   
Comment by blair@uchicago.edu [ 09/Aug/07 ]

Indices removed from Hibernate mapping:

  • membership_owner_idx (owner_id)
  • membership_type_idx( mship_type )
  • membership_member_idx (member_id)

Indices added to Hibernate mapping:

  • membership_member_and_list_idx (member_id, list_name, list_type)
  • membership_owner_list_and_type_idx (owner_id, list_name, list_type, mship_type)

These changes don't make much difference on a small, local Grouper instance using HSQLDB but Shilen's numbers suggest it will help larger and less embedded instances of Grouper.

Generated at Thu Apr 25 12:48:38 UTC 2024 using Jira 9.4.18#940018-sha1:32a59db0b032756f9bbd6a22c656d21edb3fb41f.