diff options
author | Yorick Peterse <yorickpeterse@gmail.com> | 2017-06-13 17:44:55 +0300 |
---|---|---|
committer | Yorick Peterse <yorickpeterse@gmail.com> | 2017-06-16 14:49:09 +0300 |
commit | d29347220c07ab0191cf208d3775475c7b5d71ca (patch) | |
tree | cdbf60cadb6a85c494385a97c4fe8a9497c8586c /changelogs/unreleased | |
parent | 2b34f3f20d5a5c8ecdf6e8842892cb2b4ed3c89a (diff) |
Refactor ProjectsFinder#init_collection
This changes ProjectsFinder#init_collection so it no longer relies on a
UNION. For example, to get starred projects of a user we used to run:
SELECT projects.*
FROM projects
WHERE projects.pending_delete = 'f'
AND (
projects.id IN (
SELECT projects.id
FROM projects
INNER JOIN users_star_projects
ON users_star_projects.project_id = projects.id
INNER JOIN project_authorizations
ON projects.id = project_authorizations.project_id
WHERE projects.pending_delete = 'f'
AND project_authorizations.user_id = 1
AND users_star_projects.user_id = 1
UNION
SELECT projects.id
FROM projects
INNER JOIN users_star_projects
ON users_star_projects.project_id = projects.id
WHERE projects.visibility_level IN (20, 10)
AND users_star_projects.user_id = 1
)
)
ORDER BY projects.id DESC;
With these changes the above query is turned into the following instead:
SELECT projects.*
FROM projects
INNER JOIN users_star_projects
ON users_star_projects.project_id = projects.id
WHERE projects.pending_delete = 'f'
AND (
EXISTS (
SELECT 1
FROM project_authorizations
WHERE project_authorizations.user_id = 1
AND (project_id = projects.id)
)
OR projects.visibility_level IN (20,10)
)
AND users_star_projects.user_id = 1
ORDER BY projects.id DESC;
This query in turn produces a better execution plan and takes less time,
though the difference is only a few milliseconds (this however depends
on the amount of data involved and additional conditions that may be
added).
Diffstat (limited to 'changelogs/unreleased')
-rw-r--r-- | changelogs/unreleased/refactor-projects-finder-init-collection.yml | 5 |
1 files changed, 5 insertions, 0 deletions
diff --git a/changelogs/unreleased/refactor-projects-finder-init-collection.yml b/changelogs/unreleased/refactor-projects-finder-init-collection.yml new file mode 100644 index 00000000000..c8113419f21 --- /dev/null +++ b/changelogs/unreleased/refactor-projects-finder-init-collection.yml @@ -0,0 +1,5 @@ +--- +title: Refactor ProjectsFinder#init_collection to produce more efficient queries for + retrieving projects +merge_request: +author: |