summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorErich Eckner <git@eckner.net>2018-09-28 12:03:06 +0200
committerErich Eckner <git@eckner.net>2018-09-28 12:03:06 +0200
commitbcf6609cda6fe309891c9cd6ecd3e7f36505c11b (patch)
treeefbf8cebaf507c486d6b43705288aceb449730f1
parent85926b79eb2c9078b81e71b9bd2da93dff382a2b (diff)
downloadarchweb32-bcf6609cda6fe309891c9cd6ecd3e7f36505c11b.tar.xz
buildmaster/build-slaves.php: use joins on subqueries and limit `ssh_log`.`date` to accellerate the query
-rw-r--r--buildmaster/build-slaves.php59
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`"
);