[PATCH] Remove disjunction in pkg_providers query

[Johannes Löthberg]

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 at 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/
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 1727 bytes
Desc: signature
URL: <https://lists.archlinux.org/pipermail/aur-dev/attachments/20180221/4454e924/attachment.asc>