buildmaster/build-slaves.php: use joins on subqueries and limit `ssh_log`.`date` to accellerate the query

1 files changed, 45 insertions, 14 deletions

diff --git a/buildmaster/build-slaves.php b/buildmaster/build-slaves.php
index 6d0d9e2..56853e3 100644
--- a/buildmaster/build-slaves.php
+++ b/buildmaster/build-slaves.php
@@ -21,21 +21,27 @@ $columns = array(
   "currently_building" => array(
     "label" => "currently building",
     "mysql_name" => "cb",
-    "mysql_query" => "CONCAT(`architectures`.`name`,\"/\",`package_sources`.`pkgbase`)",
+    "mysql_query" => "`ba_q`.`cb`",
+    "mysql_ba_q_name" => "cb",
+    "mysql_ba_q_query" => "CONCAT(`architectures`.`name`,\"/\",`package_sources`.`pkgbase`)",
     "sort" => "currently_building",
     "title" => "pkgbase of currently building package"
   ),
   "last_connection" => array(
     "label" => "last connection",
     "mysql_name" => "lc",
-    "mysql_query" => "MAX(`ssh_log`.`date`)",
+    "mysql_query" => "`sl_q`.`lc`",
+    "mysql_sl_q_name" => "lc",
+    "mysql_sl_q_query" => "MAX(`ssh_log`.`date`)",
     "sort" => "last_connection",
     "title" => "time of last connection"
   ),
   "building_since" => array(
     "label" => "building since",
     "mysql_name" => "bs",
-    "mysql_query" => "MAX(IF(`ssh_log`.`action`=\"get-assignment\",`ssh_log`.`date`,NULL))",
+    "mysql_query" => "`sl_q`.`bs`",
+    "mysql_sl_q_name" => "bs",
+    "mysql_sl_q_query" => "MAX(IF(`ssh_log`.`action`=\"get-assignment\",`ssh_log`.`date`,NULL))",
     "sort" => "building_since",
     "title" => "start of build"
   ),
@@ -82,26 +88,51 @@ function combine_fields($cln) {
   return $cln["mysql_query"] . " AS `" . $cln["mysql_name"] . "`";
 }
 
+function combine_ba_q_fields($cln) {
+  if (isset($cln["mysql_ba_q_query"]) && isset($cln["mysql_ba_q_name"]))
+    return $cln["mysql_ba_q_query"] . " AS `" . $cln["mysql_ba_q_name"] . "`";
+}
+
+function combine_sl_q_fields($cln) {
+  if (isset($cln["mysql_sl_q_query"]) && isset($cln["mysql_sl_q_name"]))
+    return $cln["mysql_sl_q_query"] . " AS `" . $cln["mysql_sl_q_name"] . "`";
+}
+
+function non_empty($val) {
+  return ! empty($val);
+}
+
 $result = mysql_run_query(
   "SELECT `sub_query`.* FROM (" .
     "SELECT " .
     implode(",",array_map("combine_fields",$columns)) .
-    " FROM `build_assignments`" .
-    " JOIN `package_sources`" .
-    " ON `build_assignments`.`package_source`=`package_sources`.`id`" .
-    " JOIN `architectures`" .
-    " ON `build_assignments`.`architecture`=`architectures`.`id`" .
-
-    " RIGHT JOIN `build_slaves`" .
-    " ON `build_slaves`.`currently_building`=`build_assignments`.`id`" .
+    " FROM `build_slaves`" .
     " JOIN `ssh_keys`" .
     " ON `build_slaves`.`ssh_key`=`ssh_keys`.`id`" .
     " JOIN `persons`" .
     " ON `ssh_keys`.`owner`=`persons`.`id`" .
 
-    " LEFT JOIN `ssh_log`" .
-    " ON `ssh_log`.`build_slave`=`build_slaves`.`id`" .
-    " GROUP BY `build_slaves`.`id`" .
+    " LEFT JOIN (" .
+      "SELECT " .
+      "`build_assignments`.`id` AS `id`," .
+      implode(",",array_filter(array_map("combine_ba_q_fields",$columns),"non_empty")) .
+      " FROM `build_assignments`" .
+      " JOIN `package_sources`" .
+      " ON `build_assignments`.`package_source`=`package_sources`.`id`" .
+      " JOIN `architectures`" .
+      " ON `build_assignments`.`architecture`=`architectures`.`id`" .
+    ") AS `ba_q`" .
+    " ON `ba_q`.`id`=`build_slaves`.`currently_building`" .
+
+    " LEFT JOIN (" .
+      "SELECT " .
+      "`ssh_log`.`build_slave` AS `build_slave`," .
+      implode(",",array_filter(array_map("combine_sl_q_fields",$columns),"non_empty")) .
+      " FROM `ssh_log`" .
+      " WHERE `ssh_log`.`date`>=ADDDATE(NOW(),\"-5 00:00:00\")" .
+      " GROUP BY `ssh_log`.`build_slave`" .
+    ") AS `sl_q`" .
+    " ON `sl_q`.`build_slave`=`build_slaves`.`id`" .
   ") AS `sub_query`" .
   " ORDER BY " . $order . "`sub_query`.`name`"
 );