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 <[email protected]> > --- > 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/
signature.asc
Description: signature
