From d773295186e25eff1b873a4b717e42f5c09bbaf7 Mon Sep 17 00:00:00 2001
From: Erich Eckner <git@eckner.net>
Date: Fri, 3 May 2019 10:26:11 +0200
Subject: bin/get-assignment: accellerate query by using temporary table
 holding the architectures with toolchain build orders

---
 bin/get-assignment | 50 ++++++++++++++++++++++++++------------------------
 1 file changed, 26 insertions(+), 24 deletions(-)

(limited to 'bin')

diff --git a/bin/get-assignment b/bin/get-assignment
index f6491fc..91bb407 100755
--- a/bin/get-assignment
+++ b/bin/get-assignment
@@ -257,24 +257,29 @@ next_building=$(
 
     # delete all non-toolchain packages from considered list iff any
     # toolchain package is on that list
-    printf 'DELETE `considered_build_assignments`'
+    printf 'CREATE TEMPORARY TABLE `toolchain_architectures` ('
+      printf '`id` SMALLINT NOT NULL,'
+      printf 'PRIMARY KEY (`id`)'
+    printf ');\n'
+    printf 'INSERT IGNORE INTO `toolchain_architectures` (`id`)'
+    printf ' SELECT `build_assignments`.`architecture`'
     printf ' FROM `considered_build_assignments`'
     printf ' JOIN `build_assignments`'
     printf ' ON `build_assignments`.`id`=`considered_build_assignments`.`id`'
     mysql_join_build_assignments_package_sources
-    printf ' WHERE NOT EXISTS ('
-      printf 'SELECT 1'
-      printf ' FROM `toolchain_order`'
-      printf ' WHERE `toolchain_order`.`pkgbase`=`package_sources`.`pkgbase`'
-    printf ') AND EXISTS ('
-      printf 'SELECT 1'
-      printf ' FROM `considered_build_assignments_2`'
-      printf ' JOIN `build_assignments`'
-      printf ' ON `build_assignments`.`id`=`considered_build_assignments_2`.`id`'
-      mysql_join_build_assignments_package_sources
-      printf ' JOIN `toolchain_order`'
-      printf ' ON `toolchain_order`.`pkgbase`=`package_sources`.`pkgbase`'
-    printf ');\n'
+    printf ' JOIN `toolchain_order`'
+    printf ' ON `toolchain_order`.`pkgbase`=`package_sources`.`pkgbase`;\n'
+
+    printf 'DELETE `considered_build_assignments`'
+    printf ' FROM `toolchain_architectures`'
+    printf ' JOIN `build_assignments`'
+    printf ' ON `build_assignments`.`architecture`=`toolchain_architectures`.`id`'
+    printf ' JOIN `considered_build_assignments`'
+    printf ' ON `build_assignments`.`id`=`considered_build_assignments`.`id`'
+    mysql_join_build_assignments_package_sources
+    printf ' LEFT JOIN `toolchain_order`'
+    printf ' ON `toolchain_order`.`pkgbase`=`package_sources`.`pkgbase`'
+    printf ' WHERE `toolchain_order`.`number` IS NULL;\n'
 
     printf 'DELETE `considered_build_assignments_2`'
     printf ' FROM `considered_build_assignments_2`;\n'
@@ -293,21 +298,18 @@ next_building=$(
     #  - exists unblocked before does not exist unblocked
     printf 'DELETE `considered_build_assignments`'
     printf ' FROM `considered_build_assignments`'
-# TODO: this should be architecture specific
-    printf ' WHERE EXISTS ('
-      printf 'SELECT 1'
-      printf ' FROM `considered_build_assignments_2`'
-      printf ' JOIN `build_assignments`'
-      printf ' ON `build_assignments`.`id`=`considered_build_assignments_2`.`id`'
-      mysql_join_build_assignments_package_sources
-      printf ' JOIN `toolchain_order`'
-      printf ' ON `toolchain_order`.`pkgbase`=`package_sources`.`pkgbase`'
-    printf ');\n'
+    printf ' JOIN `build_assignments`'
+    printf ' ON `build_assignments`.`id`=`considered_build_assignments`.`id`'
+    printf ' JOIN `toolchain_architectures`'
+    printf ' ON `build_assignments`.`architecture`=`toolchain_architectures`.`id`;\n'
+
     printf 'INSERT IGNORE INTO `considered_build_assignments` (`id`)'
     printf 'SELECT `considered_build_assignments_2`.`id`'
     printf ' FROM `considered_build_assignments_2`'
     printf ' JOIN `build_assignments`'
     printf ' ON `build_assignments`.`id`=`considered_build_assignments_2`.`id`'
+    printf ' JOIN `toolchain_architectures`'
+    printf ' ON `build_assignments`.`architecture`=`toolchain_architectures`.`id`'
     mysql_join_build_assignments_package_sources
     printf ' ORDER BY'
     # does it exist toolchain-blocked?
-- 
cgit v1.2.3-70-g09d2