Speed up admin front page.

* Don't show a categories summary unless asked via a link
* Don't needlessly join in questionnaire/update summaries
* Switch count(distinct()) to a subselect as it turns out
  select count(*) from (select distinct(user_id) from problem) temp;
  is a lot quicker than select count(distinct(user_id)) from problem;

5 files changed, 41 insertions, 29 deletions

diff --git a/perllib/FixMyStreet/App/Controller/Admin.pm b/perllib/FixMyStreet/App/Controller/Admin.pm
index 39d6ff72f..a61032988 100644
--- a/perllib/FixMyStreet/App/Controller/Admin.pm
+++ b/perllib/FixMyStreet/App/Controller/Admin.pm
@@ -129,7 +129,9 @@ sub index : Path : Args(0) {
       : _('n/a');
     $c->stash->{questionnaires} = \%questionnaire_counts;
 
-    $c->stash->{categories} = $c->cobrand->problems->categories_summary();
+    if ($c->get_param('show_categories')) {
+        $c->stash->{categories} = $c->cobrand->problems->categories_summary();
+    }
 
     $c->stash->{total_bodies} = $c->model('DB::Body')->count();
 
diff --git a/perllib/FixMyStreet/DB/ResultSet/Comment.pm b/perllib/FixMyStreet/DB/ResultSet/Comment.pm
index 270501efc..1b6afb819 100644
--- a/perllib/FixMyStreet/DB/ResultSet/Comment.pm
+++ b/perllib/FixMyStreet/DB/ResultSet/Comment.pm
@@ -32,15 +32,16 @@ sub timeline {
 sub summary_count {
     my ( $rs, $body_restriction ) = @_;
 
-    return $rs->to_body($body_restriction)->search(
-        undef,
-        {
-            group_by => ['me.state'],
-            select   => [ 'me.state', { count => 'me.id' } ],
-            as       => [qw/state state_count/],
-            join     => 'problem'
-        }
-    );
+    my $params = {
+        group_by => ['me.state'],
+        select   => [ 'me.state', { count => 'me.id' } ],
+        as       => [qw/state state_count/],
+    };
+    if ($body_restriction) {
+        $rs = $rs->to_body($body_restriction);
+        $params->{join} = 'problem';
+    }
+    return $rs->search(undef, $params);
 }
 
 1;
diff --git a/perllib/FixMyStreet/DB/ResultSet/Problem.pm b/perllib/FixMyStreet/DB/ResultSet/Problem.pm
index 40076d374..e9f5d0f8e 100644
--- a/perllib/FixMyStreet/DB/ResultSet/Problem.pm
+++ b/perllib/FixMyStreet/DB/ResultSet/Problem.pm
@@ -210,7 +210,10 @@ sub unique_users {
     return $rs->search( {
         state => [ FixMyStreet::DB::Result::Problem->visible_states() ],
     }, {
-        select => [ { count => { distinct => 'user_id' } } ],
+        select => [ { distinct => 'user_id' } ],
+        as     => [ 'user_id' ]
+    } )->as_subselect_rs->search( undef, {
+        select => [ { count => 'user_id' } ],
         as     => [ 'count' ]
     } )->first->get_column('count');
 }
diff --git a/perllib/FixMyStreet/DB/ResultSet/Questionnaire.pm b/perllib/FixMyStreet/DB/ResultSet/Questionnaire.pm
index db4b6e23e..bf1c68c49 100644
--- a/perllib/FixMyStreet/DB/ResultSet/Questionnaire.pm
+++ b/perllib/FixMyStreet/DB/ResultSet/Questionnaire.pm
@@ -113,6 +113,13 @@ sub send_questionnaires_period {
 sub timeline {
     my ( $rs, $restriction ) = @_;
 
+    my $attrs;
+    if (%$restriction) {
+        $attrs = {
+            -select => [qw/me.*/],
+            prefetch => [qw/problem/],
+        }
+    }
     return $rs->search(
         {
             -or => {
@@ -121,24 +128,21 @@ sub timeline {
             },
             %{ $restriction },
         },
-        {
-            -select => [qw/me.*/],
-            prefetch => [qw/problem/],
-        }
+        $attrs
     );
 }
 
 sub summary_count {
     my ( $rs, $restriction ) = @_;
 
-    return $rs->search(
-        $restriction,
-        {
-            group_by => [ \'whenanswered is not null' ],
-            select   => [ \'(whenanswered is not null)', { count => 'me.id' } ],
-            as       => [qw/answered questionnaire_count/],
-            join     => 'problem'
-        }
-    );
+    my $params = {
+        group_by => [ \'whenanswered is not null' ],
+        select => [ \'(whenanswered is not null)', { count => 'me.id' } ],
+        as => [qw/answered questionnaire_count/],
+    };
+    if (%$restriction) {
+        $params->{join} = 'problem';
+    }
+    return $rs->search($restriction, $params);
 }
 1;
diff --git a/templates/web/base/admin/index.html b/templates/web/base/admin/index.html
index 8c49a5685..dde6523a3 100644
--- a/templates/web/base/admin/index.html
+++ b/templates/web/base/admin/index.html
@@ -55,14 +55,16 @@ and to receive notices of updates.
 <h2>[% loc('Update breakdown by state') %]</h2>
 [% PROCESS states object=comments list=comments.keys.sort %]
 
-[% FOREACH category IN categories %]
-    [% IF loop.first %]
-    <h2>[% loc('Category fix rate for problems > 4 weeks old') %]</h2>
+[% IF categories.size %]
+    <h2 id="category-fix-rate">[% loc('Category fix rate for problems > 4 weeks old') %]</h2>
     <table>
         <tr><th>[% loc('Category') %]</th><th>[% loc('Total') %]</th><th>[% loc('Fixed') %]</th></tr>
-    [% END %]
+[% FOREACH category IN categories %]
         <tr><td>[% category.key %]</td><td>[% category.value.total %]</td><td>[% category.value.fixed / category.value.total * 100 | format('%.1f') %]%</td></tr>
-    [% '</table>' IF loop.last %]
+[% END %]
+    </table>
+[% ELSE %]
+<p><a href="?show_categories=1#category-fix-rate">[% loc('Category fix rate for problems > 4 weeks old') %]</a></p>
 [% END %]
 
 [% INCLUDE 'admin/footer.html' %]