#1675 Subselect gives better performance
Merged 2 years ago by tkopecek. Opened 2 years ago by tkopecek.
tkopecek/koji issue1671  into  master

file modified
+7 -4
@@ -7389,10 +7389,13 @@ 

      fields = ('id', 'name', 'version', 'release', 'arch', 'build_id')

      idx = {}

      q = """SELECT rpminfo.id, rpminfo.name, rpminfo.version, rpminfo.release, rpminfo.arch, rpminfo.build_id

-     FROM buildroot_listing

-         JOIN rpminfo ON rpminfo.buildroot_id = buildroot_listing.buildroot_id

-         JOIN build on rpminfo.build_id = build.id

-     WHERE buildroot_listing.rpm_id = %(rpm_id)s

+     FROM rpminfo, build

+     WHERE

+         rpminfo.buildroot_id IN (

rpminfo.buildroot_id could be NULL. Will it break something?

+             SELECT DISTINCT buildroot_id

+                 FROM buildroot_listing

+                 WHERE rpm_id = %(rpm_id)s)

+         AND rpminfo.build_id = build.id

          AND build.state = %(st_complete)i"""

      if limit is not None:

          q += "\nLIMIT %(limit)i"

Original query based on JOINs is 1000x slower in better cases. Sub-query
helps a lot here.

Fixes: https://pagure.io/koji/issue/1674

rpminfo.buildroot_id could be NULL. Will it break something?

rpminfo.buildroot_id could be NULL. Will it break something?

I don't think so - even in original query it will be omitted. And we don't care about rpms not used in buildroots here.

Commit c4a6f7d fixes this pull-request

Pull-Request has been merged by tkopecek

2 years ago