From a71aa2e354599950f4bd464f0f19215f1c581141 Mon Sep 17 00:00:00 2001 From: Dan McGee Date: Fri, 12 Oct 2012 11:34:49 -0500 Subject: Make wrong permissions query more efficient This removes the subplan and per-row query in favor of a LEFT JOIN where we look for non-matching rows. Tested in sqlite3 and PostgreSQL. Signed-off-by: Dan McGee --- packages/utils.py | 11 ++++++----- 1 file changed, 6 insertions(+), 5 deletions(-) (limited to 'packages') diff --git a/packages/utils.py b/packages/utils.py index ee1b56b..c29e229 100644 --- a/packages/utils.py +++ b/packages/utils.py @@ -228,12 +228,13 @@ SELECT DISTINCT id FROM packages p JOIN packages_packagerelation pr ON p.pkgbase = pr.pkgbase WHERE pr.type = %s - ) pkgs - WHERE pkgs.repo_id NOT IN ( - SELECT repo_id FROM user_profiles_allowed_repos ar + ) mp + LEFT JOIN ( + SELECT user_id, repo_id FROM user_profiles_allowed_repos ar INNER JOIN user_profiles up ON ar.userprofile_id = up.id - WHERE up.user_id = pkgs.user_id - ) + ) ur + ON mp.user_id = ur.user_id AND mp.repo_id = ur.repo_id + WHERE ur.user_id IS NULL; """ cursor = connection.cursor() cursor.execute(sql, [PackageRelation.MAINTAINER]) -- cgit v1.2.3-24-g4f1b