N+1 lookups in groups SQL

Bug #1218675 reported by Jamie Lennox on 2013-08-30
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
OpenStack Identity (keystone)
Medium
Jamie Lennox
Grizzly
Medium
Dirk Mueller

Bug Description

In the SQL for list_users_in_groups we perform

- One lookup to verify that the group exists.
- One lookup to get the users associated with this group
- N lookups to retrieve each of the N associated users

The same pattern is used in list_groups_for_user.

We can replace this with a join query.

I'm not aware if we can drop the initial verify, theoretically if the group doesn't exist then there are no entries in the join table however there may be code specifically relying on this behaviour.

Changed in keystone:
assignee: nobody → Jamie Lennox (jamielennox)
status: New → In Progress
Dolph Mathews (dolph) on 2013-08-30
Changed in keystone:
importance: Undecided → Medium

Reviewed: https://review.openstack.org/42566
Committed: http://github.com/openstack/keystone/commit/6f9b75e2c3bbbd7dfc5e2d414d7271bb4f8bcf71
Submitter: Jenkins
Branch: master

commit 6f9b75e2c3bbbd7dfc5e2d414d7271bb4f8bcf71
Author: Jamie Lennox <email address hidden>
Date: Mon Aug 19 15:00:03 2013 +1000

    Use joins instead of multiple lookups in groups sql

    Currently to determine a user's groups (or vice versa) we do
    - 1 lookup for the user.
    - 1 lookup for the association table.
    - n lookups for the n groups the user has.

    The association and groups lookup are replaced with a join query.

    Fixes: bug 1218675
    Change-Id: I560659eda1a30635399829e1c86f06734c90f9e2

Changed in keystone:
status: In Progress → Fix Committed
Thierry Carrez (ttx) on 2013-09-05
Changed in keystone:
milestone: none → havana-3
status: Fix Committed → Fix Released

Reviewed: https://review.openstack.org/45629
Committed: http://github.com/openstack/keystone/commit/53ff4451c31c888e6cafac9b3a44599601cf6bdc
Submitter: Jenkins
Branch: stable/grizzly

commit 53ff4451c31c888e6cafac9b3a44599601cf6bdc
Author: Jamie Lennox <email address hidden>
Date: Mon Aug 19 15:00:03 2013 +1000

    Use joins instead of multiple lookups in groups sql

    Currently to determine a user's groups (or vice versa) we do
    - 1 lookup for the user.
    - 1 lookup for the association table.
    - n lookups for the n groups the user has.

    The association and groups lookup are replaced with a join query.

    Fixes: bug 1218675
    Change-Id: I560659eda1a30635399829e1c86f06734c90f9e2
    (cherry picked from commit 6f9b75e2c3bbbd7dfc5e2d414d7271bb4f8bcf71)

Thierry Carrez (ttx) on 2013-10-17
Changed in keystone:
milestone: havana-3 → 2013.2
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers