gitlabhq/gitlabhq — pull request #2438 by jirutka (2012-12-30 19:44:54+00:00)
Title: Optimize query for keys of repository members

There’re three methods in Authority module to retrieve SSH key identifiers of repository’s members, differs only in `project_access` role, but called all one by one from GitoliteConfig class. This generates three queries per project (e.g. when adding/removing SSH key, there’re often many projects that needs to update). Besides that these methods violates DRY principle.

We can simply query just pair of `project_access` and `identifier` (e.g. keys for all “roles” at once) and then get desired identifiers by the `project_access`. The query is generated three times as before, but second and third one are served from cache.

**Before:**
```log
Key Load (4.9ms) SELECT "keys".* FROM "keys" INNER JOIN "users" ON "users"."id" = "keys"."user_id" INNER JOIN "users_projects" ON "users_projects"."user_id" = "users"."id" WHERE (users_projects.project_id = 14 AND users_projects.project_access = 20)
Key Load (2.4ms) SELECT "keys".* FROM "keys" WHERE "keys"."project_id" = 14
Key Load (5.7ms) SELECT "keys".* FROM "keys" INNER JOIN "users" ON "users"."id" = "keys"."user_id" INNER JOIN "users_projects" ON "users_projects"."user_id" = "users"."id" WHERE (users_projects.project_id = 14 AND users_projects.project_access = 30)
Key Load (5.2ms) SELECT "keys".* FROM "keys" INNER JOIN "users" ON "users"."id" = "keys"."user_id" INNER JOIN "users_projects" ON "users_projects"."user_id" = "users"."id" WHERE (users_projects.project_id = 14 AND users_projects.project_access = 40)
ProtectedBranch Load (2.2ms) SELECT "protected_branches".* FROM "protected_branches" WHERE "protected_branches"."project_id" = 14
```

**After:**
```log
UsersProject Load (3.1ms) SELECT keys.identifier as key_identifier, project_access FROM "users_projects" INNER JOIN "users" ON "users"."id" = "users_projects"."user_id" INNER JOIN "keys" ON "keys"."user_id" = "users"."id" WHERE "users_projects"."project_id" = 14
(1.8ms) SELECT identifier FROM "keys" WHERE "keys"."project_id" = 14
CACHE (0.0ms) SELECT keys.identifier as key_identifier, project_access FROM "users_projects" INNER JOIN "users" ON "users"."id" = "users_projects"."user_id" INNER JOIN "keys" ON "keys"."user_id" = "users"."id" WHERE "users_projects"."project_id" = 14
CACHE (0.0ms) SELECT identifier FROM "keys" WHERE "keys"."project_id" = 14
CACHE (0.0ms) SELECT keys.identifier as key_identifier, project_access FROM "users_projects" INNER JOIN "users" ON "users"."id" = "users_projects"."user_id" INNER JOIN "keys" ON "keys"."user_id" = "users"."id" WHERE "users_projects"."project_id" = 14
CACHE (0.1ms) SELECT identifier FROM "keys" WHERE "keys"."project_id" = 14
ProtectedBranch Load (1.4ms) SELECT "protected_branches".* FROM "protected_branches" WHERE "protected_branches"."project_id" = 14
```