[OSSTEST PATCH 06/11] sg-report-host-history: Do the main query per host

[Ian Jackson <ian.jackson@xxxxxxxxxxxxx>]

In f6001d628c3b3fd42b10cd15351981a04bc02572 we combined these
queries into one:
  sg-report-host-history: Aggregate runvars query for all hosts

Now that we have an index, there is a faster way for the db to do this
query: via that index.  But it doesn't like to do that if be aggregate
the queries.  Experimentally, doing this query separately once per
host is significantly faster.

Also, later, it will allow us to parallelise this work.

So, we undo that.  (Not by reverting, though.)

Signed-off-by: Ian Jackson <ian.jackson@xxxxxxxxxxxxx>
---
 schema/runvars-host-index.sql |  2 +-
 sg-report-host-history        | 27 +++++++++------------------
 2 files changed, 10 insertions(+), 19 deletions(-)

diff --git a/schema/runvars-host-index.sql b/schema/runvars-host-index.sql
index fec0b960..cd6a1f9e 100644
--- a/schema/runvars-host-index.sql
+++ b/schema/runvars-host-index.sql
@@ -1,4 +1,4 @@
--- ##OSSTEST## 009 Preparatory
+-- ##OSSTEST## 009 Needed
 --
 -- This index helps sg-report-host-history find relevant flights.
 
diff --git a/sg-report-host-history b/sg-report-host-history
index 25a0c847..ab88828e 100755
--- a/sg-report-host-history
+++ b/sg-report-host-history
@@ -165,34 +165,25 @@ sub jobquery ($$$) {
 our %hosts;
 
 sub mainquery () {
-    our $valcond = join " OR ", map { "val = ?" } keys %hosts;
-    our @params = keys %hosts;
-
     our $runvarq //= db_prepare(<<END);
-       SELECT flight, job, name, val, status
+       SELECT flight, job, name, status
          FROM runvars
           JOIN jobs USING (flight, job)
-        WHERE $namecond
-          AND ($valcond)
+        WHERE (name = 'host' OR name LIKE '%_host')
+          AND val = ?
           AND $flightcond
            AND $restrictflight_cond
            AND flight > ?
         ORDER BY flight DESC
-        LIMIT ($limit * 3 + 100) * ?
+         LIMIT $limit * 2
 END
+    foreach my $host (sort keys %hosts) {
+       print DEBUG "MAINQUERY $host...\n";
+       $runvarq->execute($host, $minflight);
 
-    push @params, $minflight;
-    push @params, scalar keys %hosts;
-
-    print DEBUG "MAINQUERY...\n";
-    $runvarq->execute(@params);
-
-    print DEBUG "FIRST PASS\n";
-    while (my $jr= $runvarq->fetchrow_hashref()) {
-       print DEBUG " $jr->{flight}.$jr->{job} ";
-       push @{ $hosts{$jr->{val}} }, $jr;
+       $hosts{$host} = $runvarq->fetchall_arrayref({});
+       print DEBUG "MAINQUERY $host got ".(scalar @{ $hosts{$host} })."\n";
     }
-    print DEBUG "\n";
 }
 
 sub reporthost ($) {
-- 
2.20.1