buildmaster/build-list.php: use temporary tables to accellerate query

1 files changed, 246 insertions, 149 deletions

diff --git a/buildmaster/build-list.php b/buildmaster/build-list.php
index a574d39..1962c07 100644
--- a/buildmaster/build-list.php
+++ b/buildmaster/build-list.php
@@ -265,158 +265,255 @@ function combine_fields($cln) {
   return $cln["mysql_query"] . " AS `" . $cln["mysql_name"] . "`";
 }
 
-$result = mysql_run_query(
-  "SELECT " .
-  implode(",",array_map("combine_fields",$columns)) .
-  " FROM" .
-  " (" .
-    "SELECT DISTINCT " .
-    "`build_assignments`.`id`," .
-    "`build_assignments`.`is_blocked`," .
-    "`build_assignments`.`is_broken`," .
-    "`build_assignments`.`priority`," .
-    "`build_assignments`.`currently_blocking`," .
-    "`package_sources`.`pkgbase`," .
-    "`package_sources`.`git_revision`," .
-    "`package_sources`.`mod_git_revision`," .
-    "`package_sources`.`uses_upstream`," .
-    "`package_sources`.`uses_modification`," .
-    "`package_sources`.`commit_time`," .
-    "`upstream_repositories`.`name` AS `package_repository`," .
-    "`git_repositories`.`name` AS `git_repository`," .
-    "`architectures`.`name` AS `arch`" .
-    " FROM `build_assignments`" .
-    mysql_join_build_assignments_architectures() .
-    mysql_join_build_assignments_package_sources() .
-    mysql_join_package_sources_upstream_repositories() .
-    mysql_join_upstream_repositories_git_repositories() .
-    mysql_join_build_assignments_binary_packages() .
-    mysql_join_binary_packages_binary_packages_in_repositories() .
-    mysql_join_binary_packages_in_repositories_repositories() .
-    " WHERE `repositories`.`name`=\"build-list\"" .
-  ") AS `ba_q`".
-  " LEFT JOIN" .
-  " (" .
-    "SELECT " .
-    "`dependent_bp`.`build_assignment`," .
-    "COUNT(DISTINCT `dependency_bp`.`build_assignment`) AS `run_dependencies_pending`" .
-    " FROM `binary_packages` AS `dependent_bp`" .
-    mysql_join_binary_packages_dependencies('dependent_bp') .
-    mysql_join_dependencies_dependency_types() .
-    mysql_join_dependencies_install_target_providers() .
+mysql_run_query(
+  'CREATE TEMPORARY TABLE `ba_q`(' .
+  '`id` BIGINT,' .
+  '`is_blocked` BIT,' .
+  '`is_broken` BIT,' .
+  '`priority` SMALLINT,' .
+  '`currently_blocking` MEDIUMINT,' .
+  '`pkgbase` VARCHAR(64),' .
+  '`git_revision` VARCHAR(40),' .
+  '`mod_git_revision` VARCHAR(40),' .
+  '`uses_upstream` BIT,' .
+  '`uses_modification` BIT,' .
+  '`commit_time` TIMESTAMP,' .
+  '`package_repository` VARCHAR(64),' .
+  '`git_repository` VARCHAR(64),' .
+  '`arch` VARCHAR(16),' .
+  'PRIMARY KEY `id`(`id`)' .
+  ')'
+);
+
+mysql_run_query(
+  'INSERT INTO `ba_q`' .
+  ' SELECT DISTINCT' .
+  ' `build_assignments`.`id`,' .
+  '`build_assignments`.`is_blocked`,' .
+  '`build_assignments`.`is_broken`,' .
+  '`build_assignments`.`priority`,' .
+  '`build_assignments`.`currently_blocking`,' .
+  '`package_sources`.`pkgbase`,' .
+  '`package_sources`.`git_revision`,' .
+  '`package_sources`.`mod_git_revision`,' .
+  '`package_sources`.`uses_upstream`,' .
+  '`package_sources`.`uses_modification`,' .
+  '`package_sources`.`commit_time`,' .
+  '`upstream_repositories`.`name`,' .
+  '`git_repositories`.`name`,' .
+  '`architectures`.`name`' .
+  ' FROM `build_assignments`' .
+  mysql_join_build_assignments_architectures() .
+  mysql_join_build_assignments_package_sources() .
+  mysql_join_package_sources_upstream_repositories() .
+  mysql_join_upstream_repositories_git_repositories() .
+  mysql_join_build_assignments_binary_packages() .
+  mysql_join_binary_packages_binary_packages_in_repositories() .
+  mysql_join_binary_packages_in_repositories_repositories() .
+  ' WHERE `repositories`.`name`="build-list"'
+);
+
+mysql_run_query(
+  'CREATE TEMPORARY TABLE `rd_q`(' .
+  '`build_assignment` BIGINT,' .
+  '`run_dependencies_pending` MEDIUMINT,' .
+  'PRIMARY KEY `build_assignment`(`build_assignment`)' .
+  ')'
+);
+
+mysql_run_query(
+  'INSERT INTO `rd_q`' .
+  'SELECT' .
+  ' `dependent_bp`.`build_assignment`,' .
+  'COUNT(DISTINCT `dependency_bp`.`build_assignment`)' .
+  ' FROM `binary_packages` AS `dependent_bp`' .
+// only consider ba_q build assignments!
+  mysql_join_binary_packages_build_assignments('dependent_bp','ba_q') .
+  mysql_join_binary_packages_dependencies('dependent_bp') .
+  mysql_join_dependencies_dependency_types() .
+  mysql_join_dependencies_install_target_providers() .
+  mysql_join_install_target_providers_binary_packages('','dependency_bp') .
+  mysql_join_binary_packages_binary_packages_in_repositories('dependency_bp') .
+  mysql_join_binary_packages_build_assignments('dependent_bp','dependent_ba') .
+  mysql_join_binary_packages_build_assignments('dependency_bp','dependency_ba') .
+  ' JOIN `architecture_compatibilities` AS `ac_a`'.
+  ' ON `ac_a`.`fully_compatible`'.
+  ' AND `ac_a`.`built_for`=`dependency_ba`.`architecture`'.
+  ' JOIN `architecture_compatibilities` AS `ac_b`'.
+  ' ON `ac_b`.`fully_compatible`'.
+  ' AND `ac_b`.`built_for`=`dependent_ba`.`architecture`'.
+  ' AND `ac_b`.`runs_on`=`ac_a`.`runs_on`'.
+  mysql_join_binary_packages_in_repositories_repositories() .
+  ' WHERE `dependency_bp`.`build_assignment` != `dependent_bp`.`build_assignment`' .
+  ' AND `dependency_types`.`relevant_for_building`' .
+  ' AND `dependency_types`.`relevant_for_binary_packages`' .
+  ' AND `repositories`.`name`="build-list"' .
+  ' GROUP BY `dependent_bp`.`build_assignment`'
+);
+
+mysql_run_query(
+  'CREATE TEMPORARY TABLE `md_q`(' .
+  '`build_assignment` BIGINT,' .
+  '`make_dependencies_pending` MEDIUMINT,' .
+  'PRIMARY KEY `build_assignment`(`build_assignment`)' .
+  ')'
+);
+
+mysql_run_query(
+  'INSERT INTO `md_q`' .
+  ' SELECT' .
+  ' `dependent_bp`.`build_assignment`,' .
+  'COUNT(DISTINCT `dependencies`.`id`)' .
+  ' FROM `binary_packages` AS `dependent_bp`' .
+// only consider ba_q build assignments!
+  mysql_join_binary_packages_build_assignments('dependent_bp','ba_q') .
+  mysql_join_binary_packages_dependencies('dependent_bp') .
+  mysql_join_dependencies_dependency_types() .
+  mysql_join_binary_packages_build_assignments('dependent_bp','dependent_ba') .
+  ' JOIN `architecture_compatibilities` AS `ac_b`' .
+  ' ON `ac_b`.`fully_compatible`' .
+  ' AND `ac_b`.`built_for`=`dependent_ba`.`architecture`' .
+  ' WHERE NOT EXISTS(' .
+    'SELECT 1 FROM `install_target_providers`' .
     mysql_join_install_target_providers_binary_packages('','dependency_bp') .
-    mysql_join_binary_packages_binary_packages_in_repositories('dependency_bp') .
-    mysql_join_binary_packages_build_assignments('dependent_bp','dependent_ba') .
+    mysql_join_binary_packages_binary_packages_in_repositories('dependency_bp','dependency_bpir') .
+    mysql_join_binary_packages_in_repositories_repositories('dependency_bpir') .
     mysql_join_binary_packages_build_assignments('dependency_bp','dependency_ba') .
-    " JOIN `architecture_compatibilities` AS `ac_a`".
-    " ON `ac_a`.`fully_compatible`".
-    " AND `ac_a`.`built_for`=`dependency_ba`.`architecture`".
-    " JOIN `architecture_compatibilities` AS `ac_b`".
-    " ON `ac_b`.`fully_compatible`".
-    " AND `ac_b`.`built_for`=`dependent_ba`.`architecture`".
-    " AND `ac_b`.`runs_on`=`ac_a`.`runs_on`".
-    mysql_join_binary_packages_in_repositories_repositories() .
-    " WHERE `dependency_bp`.`build_assignment` != `dependent_bp`.`build_assignment`" .
-    " AND `dependency_types`.`relevant_for_building`" .
-    " AND `dependency_types`.`relevant_for_binary_packages`" .
-    " AND `repositories`.`name`=\"build-list\"" .
-    " GROUP BY `dependent_bp`.`build_assignment`" .
-  ") AS `rd_q` ON `rd_q`.`build_assignment`=`ba_q`.`id`" .
-  " LEFT JOIN" .
-  " (" .
-    "SELECT " .
-    "`dependent_bp`.`build_assignment`," .
-    "COUNT(DISTINCT `dependencies`.`id`) AS `make_dependencies_pending`" .
-    " FROM `binary_packages` AS `dependent_bp`" .
-    mysql_join_binary_packages_dependencies('dependent_bp') .
-    mysql_join_dependencies_dependency_types() .
-    mysql_join_binary_packages_build_assignments('dependent_bp','dependent_ba') .
-    " JOIN `architecture_compatibilities` AS `ac_b`".
-    " ON `ac_b`.`fully_compatible`".
-    " AND `ac_b`.`built_for`=`dependent_ba`.`architecture`".
-    " WHERE NOT EXISTS(" .
-      "SELECT 1 FROM `install_target_providers`" .
-      mysql_join_install_target_providers_binary_packages('','dependency_bp') .
-      mysql_join_binary_packages_binary_packages_in_repositories('dependency_bp','dependency_bpir') .
-      mysql_join_binary_packages_in_repositories_repositories('dependency_bpir') .
-      mysql_join_binary_packages_build_assignments('dependency_bp','dependency_ba') .
-      " JOIN `architecture_compatibilities` AS `ac_a`".
-      " ON `ac_a`.`fully_compatible`".
-      " AND `ac_a`.`built_for`=`dependency_ba`.`architecture`".
-      " WHERE `install_target_providers`.`install_target` = `dependencies`.`depending_on`" .
-      " AND `repositories`.`is_on_master_mirror`" .
-      " AND `ac_b`.`runs_on`=`ac_a`.`runs_on`".
-    ")" .
-    " AND `dependency_types`.`relevant_for_building`" .
-    " AND NOT `dependency_types`.`relevant_for_binary_packages`" .
-    " GROUP BY `dependent_bp`.`build_assignment`" .
-  ") AS `md_q` ON `md_q`.`build_assignment`=`ba_q`.`id`" .
-  " LEFT JOIN" .
-  " (" .
-    "SELECT " .
-    "`build_dependency_loops`.`build_assignment`," .
-    "COUNT(1) AS `loops`" .
-    " FROM `build_dependency_loops`" .
-    " GROUP BY `build_dependency_loops`.`build_assignment`" .
-  ") AS `l_q` ON `l_q`.`build_assignment`=`ba_q`.`id`" .
-  " LEFT JOIN" .
-  " (" .
-    "SELECT " .
-    "`rfb`.`build_assignment`," .
-    "GROUP_CONCAT(" .
-      "CONCAT(" .
-        "\"<a href=\\\"https://buildmaster.archlinux32.org/build-logs/error/\"," .
-        mysql_url_encode("`rfb`.`log_file`") . "," .
-        "\"\\\">\"," .
-        "`fail_reasons`.`name`," .
-        "\"</a>\"" .
-      ")" .
-      " ORDER BY `fail_reasons`.`name`" .
-    ") AS `fail_reasons_print`," .
-    "CONCAT(" .
-      "\",\"," .
-      "GROUP_CONCAT(" .
-        "`fail_reasons`.`name`" .
-      ")," .
-      "\",\"" .
-    ") AS `fail_reasons_raw`" .
-    " FROM (" .
-      "SELECT " .
-      "`failed_builds`.`build_assignment`," .
-      "`failed_builds`.`reason`," .
-      "MAX(`failed_builds`.`date`) AS `max_date`" .
-      " FROM `failed_builds`" .
-      " GROUP BY `failed_builds`.`build_assignment`,`failed_builds`.`reason`" .
-    ") AS `cfb`" .
-    " JOIN" .
-    " (" .
-      "SELECT DISTINCT " .
-      "`failed_builds`.*" .
-      " FROM `failed_builds`" .
-      " GROUP BY `failed_builds`.`build_assignment`,`failed_builds`.`reason`,`failed_builds`.`date`" .
-    ") AS `rfb`" .
-    " ON `cfb`.`build_assignment`=`rfb`.`build_assignment`" .
-    " AND `cfb`.`reason`=`rfb`.`reason`" .
-    " AND `cfb`.`max_date`=`rfb`.`date`" .
+    ' JOIN `architecture_compatibilities` AS `ac_a`' .
+    ' ON `ac_a`.`fully_compatible`' .
+    ' AND `ac_a`.`built_for`=`dependency_ba`.`architecture`' .
+    ' WHERE `install_target_providers`.`install_target` = `dependencies`.`depending_on`' .
+    ' AND `repositories`.`is_on_master_mirror`' .
+    ' AND `ac_b`.`runs_on`=`ac_a`.`runs_on`' .
+  ')' .
+  ' AND `dependency_types`.`relevant_for_building`' .
+  ' AND NOT `dependency_types`.`relevant_for_binary_packages`' .
+  ' GROUP BY `dependent_bp`.`build_assignment`'
+);
+
+mysql_run_query(
+  'CREATE TEMPORARY TABLE `l_q`(' .
+    '`build_assignment` BIGINT,' .
+    '`loops` MEDIUMINT,' .
+    'PRIMARY KEY `build_assignment`(`build_assignment`)' .
+  ')'
+);
+
+mysql_run_query(
+  'INSERT INTO `l_q`' .
+  ' SELECT' .
+  ' `build_dependency_loops`.`build_assignment`,' .
+  'COUNT(1)' .
+  ' FROM `build_dependency_loops`' .
+// no need to filter for ba_q build assignments: only *those* can be in loops anyways
+  ' GROUP BY `build_dependency_loops`.`build_assignment`'
+);
+
+mysql_run_query(
+  'CREATE TEMPORARY TABLE `fr_q`(' .
+    '`build_assignment` BIGINT,' .
+    '`fail_reasons_print` TEXT,' .
+    '`fail_reasons_raw` TEXT,' .
+    'PRIMARY KEY `build_assignment`(`build_assignment`)' .
+  ')'
+);
+
+mysql_run_query(
+  'INSERT INTO `fr_q`' .
+  ' SELECT' .
+  ' `rfb`.`build_assignment`,' .
+  'GROUP_CONCAT(' .
+    'CONCAT(' .
+      '"<a href=\"https://buildmaster.archlinux32.org/build-logs/error/",' .
+        mysql_url_encode('`rfb`.`log_file`') . ',' .
+      '"\">",' .
+      '`fail_reasons`.`name`,' .
+      '"</a>"' .
+    ')' .
+    ' ORDER BY `fail_reasons`.`name`' .
+  '),' .
+  'CONCAT(' .
+    '",",' .
+    'GROUP_CONCAT(' .
+      '`fail_reasons`.`name`' .
+    '),' .
+    '","' .
+  ')' .
+  ' FROM (' .
+    'SELECT ' .
+    '`failed_builds`.`build_assignment`,' .
+    '`failed_builds`.`reason`,' .
+    'MAX(`failed_builds`.`date`) AS `max_date`' .
+    ' FROM `failed_builds`' .
+// no need to filter for ba_q build assignments: only *those* can be failed anyways
+    ' GROUP BY `failed_builds`.`build_assignment`,`failed_builds`.`reason`' .
+  ') AS `cfb`' .
+  ' JOIN' .
+  ' (' .
+    'SELECT DISTINCT ' .
+    '`failed_builds`.*' .
+    ' FROM `failed_builds`' .
+    ' GROUP BY `failed_builds`.`build_assignment`,`failed_builds`.`reason`,`failed_builds`.`date`' .
+  ') AS `rfb`' .
+  ' ON `cfb`.`build_assignment`=`rfb`.`build_assignment`' .
+  ' AND `cfb`.`reason`=`rfb`.`reason`' .
+  ' AND `cfb`.`max_date`=`rfb`.`date`' .
     mysql_join_failed_builds_fail_reasons('rfb') .
-    " GROUP BY `rfb`.`build_assignment`" .
-  ") AS `fr_q` ON `fr_q`.`build_assignment`=`ba_q`.`id`" .
-  " LEFT JOIN" .
-  " (" .
-    "SELECT " .
-    "`failed_builds`.`build_assignment`," .
-    "COUNT(`failed_builds`.`id`) AS `trials`" .
-    " FROM `failed_builds`" .
-    " GROUP BY `failed_builds`.`build_assignment`" .
-  ") AS `t_q` ON `t_q`.`build_assignment`=`ba_q`.`id`" .
-  " LEFT JOIN" .
-  " (" .
-    "SELECT " .
-    "`build_slaves`.`currently_building`," .
-    "GROUP_CONCAT(`build_slaves`.`name`) AS `build_slave`" .
-    " FROM `build_slaves`" .
-    " GROUP BY `build_slaves`.`currently_building`" .
-  ") AS `bs_q` ON `bs_q`.`currently_building`=`ba_q`.`id`" .
+  ' GROUP BY `rfb`.`build_assignment`'
+);
+
+mysql_run_query(
+  'CREATE TEMPORARY TABLE `t_q`(' .
+    '`build_assignment` BIGINT,' .
+    '`trials` MEDIUMINT,' .
+    'PRIMARY KEY `build_assignment`(`build_assignment`)' .
+  ')'
+);
+
+mysql_run_query(
+  'INSERT INTO `t_q`' .
+  ' SELECT' .
+  ' `failed_builds`.`build_assignment`,' .
+  'COUNT(`failed_builds`.`id`)' .
+  ' FROM `failed_builds`' .
+// no need to filter for ba_q build assignments: only *those* can be failed anyways
+  ' GROUP BY `failed_builds`.`build_assignment`'
+);
+
+mysql_run_query(
+  'CREATE TEMPORARY TABLE `bs_q` (' .
+    '`currently_building` BIGINT,' .
+    '`build_slave` VARCHAR(32),' .
+    'PRIMARY KEY `currently_building`(`currently_building`)' .
+  ')'
+);
+
+mysql_run_query(
+  'INSERT INTO `bs_q`' .
+  ' SELECT' .
+  ' `build_slaves`.`currently_building`,' .
+  'GROUP_CONCAT(`build_slaves`.`name`) AS `build_slave`' .
+  ' FROM `build_slaves`' .
+  ' WHERE NOT `build_slaves`.`currently_building` IS NULL' .
+  ' GROUP BY `build_slaves`.`currently_building`'
+);
+
+$result = mysql_run_query(
+  'SELECT ' .
+  implode(',', array_map('combine_fields', $columns)) .
+  ' FROM `ba_q`'.
+  ' LEFT JOIN `rd_q`' .
+  ' ON `rd_q`.`build_assignment`=`ba_q`.`id`' .
+  ' LEFT JOIN `md_q`' .
+  ' ON `md_q`.`build_assignment`=`ba_q`.`id`' .
+  ' LEFT JOIN `l_q`' .
+  ' ON `l_q`.`build_assignment`=`ba_q`.`id`' .
+  ' LEFT JOIN `fr_q`' .
+  ' ON `fr_q`.`build_assignment`=`ba_q`.`id`' .
+  ' LEFT JOIN `t_q`' .
+  ' ON `t_q`.`build_assignment`=`ba_q`.`id`' .
+  ' LEFT JOIN `bs_q`' .
+  ' ON `bs_q`.`currently_building`=`ba_q`.`id`' .
   $filter .
   " ORDER BY " . $order . "`trials` " . $direction . ",`dependencies_pending` " . $direction . ",`is_blocked` " . $direction . ",`pkgbase` " . $direction
 );