[PATCH] Remove disjunction in pkg_providers query
For some reason, running the SELECT .. WHERE .. OR .. query takes e.g. 58ms on a randomly generated db for some dependency name. Splitting the OR into two dedicated queries and UNIONing the result takes only 0.42ms. On the Arch Linux installation, searching for the providers of e.g. mongodb takes >=110ms when not cached by the query cache. The new query takes <1ms even when not cached. Signed-off-by: Florian Pritz <bluewind@xinu.at> --- web/lib/pkgfuncs.inc.php | 6 ++++-- 1 file changed, 4 insertions(+), 2 deletions(-) diff --git a/web/lib/pkgfuncs.inc.php b/web/lib/pkgfuncs.inc.php index d022ebe..1dd8481 100644 --- a/web/lib/pkgfuncs.inc.php +++ b/web/lib/pkgfuncs.inc.php @@ -212,10 +212,12 @@ function pkg_groups($pkgid) { function pkg_providers($name) { $dbh = DB::connect(); $q = "SELECT p.ID, p.Name FROM Packages p "; + $q.= "WHERE p.Name = " . $dbh->quote($name) . " "; + $q.= "UNION "; + $q = "SELECT p.ID, p.Name FROM Packages p "; $q.= "LEFT JOIN PackageRelations pr ON pr.PackageID = p.ID "; $q.= "LEFT JOIN RelationTypes rt ON rt.ID = pr.RelTypeID "; - $q.= "WHERE p.Name = " . $dbh->quote($name) . " "; - $q.= "OR (rt.Name = 'provides' "; + $q.= "WHERE (rt.Name = 'provides' "; $q.= "AND pr.RelName = " . $dbh->quote($name) . ")"; $q.= "UNION "; $q.= "SELECT 0, Name FROM OfficialProviders "; -- 2.16.2
Quoting Florian Pritz (2018-02-21 15:49:05)
For some reason, running the SELECT .. WHERE .. OR .. query takes e.g. 58ms on a randomly generated db for some dependency name. Splitting the OR into two dedicated queries and UNIONing the result takes only 0.42ms.
On the Arch Linux installation, searching for the providers of e.g. mongodb takes >=110ms when not cached by the query cache. The new query takes <1ms even when not cached.
Signed-off-by: Florian Pritz <bluewind@xinu.at> --- web/lib/pkgfuncs.inc.php | 6 ++++-- 1 file changed, 4 insertions(+), 2 deletions(-)
diff --git a/web/lib/pkgfuncs.inc.php b/web/lib/pkgfuncs.inc.php index d022ebe..1dd8481 100644 --- a/web/lib/pkgfuncs.inc.php +++ b/web/lib/pkgfuncs.inc.php @@ -212,10 +212,12 @@ function pkg_groups($pkgid) { function pkg_providers($name) { $dbh = DB::connect(); $q = "SELECT p.ID, p.Name FROM Packages p "; + $q.= "WHERE p.Name = " . $dbh->quote($name) . " "; + $q.= "UNION "; + $q = "SELECT p.ID, p.Name FROM Packages p "; $q.= "LEFT JOIN PackageRelations pr ON pr.PackageID = p.ID "; $q.= "LEFT JOIN RelationTypes rt ON rt.ID = pr.RelTypeID "; - $q.= "WHERE p.Name = " . $dbh->quote($name) . " "; - $q.= "OR (rt.Name = 'provides' "; + $q.= "WHERE (rt.Name = 'provides' "; $q.= "AND pr.RelName = " . $dbh->quote($name) . ")"; $q.= "UNION "; $q.= "SELECT 0, Name FROM OfficialProviders ";
I don't currently have a aurweb development environment set up to actually test it, but it looks good to me. -- Sincerely, Johannes Löthberg PGP Key ID: 0x50FB9B273A9D0BB5 PGP Key FP: 5134 EF9E AF65 F95B 6BB1 608E 50FB 9B27 3A9D 0BB5 https://theos.kyriasis.com/~kyrias/
On Wed, 21 Feb 2018 at 15:49:05, Florian Pritz wrote:
For some reason, running the SELECT .. WHERE .. OR .. query takes e.g. 58ms on a randomly generated db for some dependency name. Splitting the OR into two dedicated queries and UNIONing the result takes only 0.42ms.
On the Arch Linux installation, searching for the providers of e.g. mongodb takes >=110ms when not cached by the query cache. The new query takes <1ms even when not cached.
Signed-off-by: Florian Pritz <bluewind@xinu.at> --- web/lib/pkgfuncs.inc.php | 6 ++++-- 1 file changed, 4 insertions(+), 2 deletions(-)
Merged, thanks.
participants (3)
-
Florian Pritz
-
Johannes Löthberg
-
Lukas Fleischer