Speed up list page query.

7 files changed, 28 insertions, 7 deletions

diff --git a/app/controllers/body_controller.rb b/app/controllers/body_controller.rb
index a50b9e906..b2addd045 100644
--- a/app/controllers/body_controller.rb
+++ b/app/controllers/body_controller.rb
@@ -4,7 +4,7 @@
 # Copyright (c) 2007 UK Citizens Online Democracy. All rights reserved.
 # Email: francis@mysociety.org; WWW: http://www.mysociety.org/
 #
-# $Id: body_controller.rb,v 1.16 2008-09-07 17:43:11 francis Exp $
+# $Id: body_controller.rb,v 1.17 2008-09-13 15:35:37 francis Exp $
 
 class BodyController < ApplicationController
     # XXX tidy this up with better error messages, and a more standard infrastructure for the redirect to canonical URL
@@ -48,7 +48,7 @@ class BodyController < ApplicationController
                 and public_body_tags.name in (' + category_list + ')) = 0']
         elsif @tag.size == 1
             @tag.upcase!
-            conditions = ['upper(substr(name, 1, 1)) = ?', @tag]
+            conditions = ['first_letter = ?', @tag]
         else
             conditions = ['(select count(*) from public_body_tags where public_body_tags.public_body_id = public_bodies.id
                 and public_body_tags.name = ?) > 0', @tag]
diff --git a/app/models/public_body.rb b/app/models/public_body.rb
index 8ce75c71c..d5a059e63 100644
--- a/app/models/public_body.rb
+++ b/app/models/public_body.rb
@@ -23,7 +23,7 @@
 # Copyright (c) 2007 UK Citizens Online Democracy. All rights reserved.
 # Email: francis@mysociety.org; WWW: http://www.mysociety.org/
 #
-# $Id: public_body.rb,v 1.104 2008-09-07 17:01:49 francis Exp $
+# $Id: public_body.rb,v 1.105 2008-09-13 15:35:37 francis Exp $
 
 require 'csv'
 require 'set'
@@ -110,6 +110,12 @@ class PublicBody < ActiveRecord::Base
         Hash[*self.categories_with_description.map() { |a| [a[0],a[2]] }.flatten]
     end
 
+    # Set the first letter, which is used for faster queries
+    before_save(:set_first_letter)
+    def set_first_letter
+        self.first_letter = self.name[0,1].upcase
+    end
+
     # Strip whitespace for everything, see http://railsforum.com/viewtopic.php?id=969
     before_save(:clean_whitespace)
     def clean_whitespace
@@ -145,7 +151,7 @@ class PublicBody < ActiveRecord::Base
     end
 
     acts_as_versioned
-    self.non_versioned_columns << 'created_at' << 'updated_at'
+    self.non_versioned_columns << 'created_at' << 'updated_at' << 'first_letter'
     class Version
         attr_accessor :created_at
     end
diff --git a/app/views/admin_public_body/show.rhtml b/app/views/admin_public_body/show.rhtml
index 8b4224880..eac38cfa8 100644
--- a/app/views/admin_public_body/show.rhtml
+++ b/app/views/admin_public_body/show.rhtml
@@ -22,7 +22,7 @@
 <tr>
 <% 
     history_columns = PublicBody.content_columns
-    history_columns.delete_if {|c| ['created_at', 'updated_at'].include?(c.name)}
+    history_columns.delete_if {|c| ['created_at', 'updated_at', 'first_letter'].include?(c.name)}
     for column in history_columns %>
   <th><%= column.human_name %></th>
 <% end %>
diff --git a/config/environment.rb b/config/environment.rb
index 6d8606155..9edc009fa 100644
--- a/config/environment.rb
+++ b/config/environment.rb
@@ -119,6 +119,6 @@ module WillPaginate
 end
 
 # XXX temp debug for SQL logging production sites
-# ActiveRecord::Base.logger = Logger.new(STDOUT)
+ActiveRecord::Base.logger = Logger.new(STDOUT)
 
 
diff --git a/db/migrate/066_add_first_letter.rb b/db/migrate/066_add_first_letter.rb
new file mode 100644
index 000000000..7878098e0
--- /dev/null
+++ b/db/migrate/066_add_first_letter.rb
@@ -0,0 +1,12 @@
+class AddFirstLetter < ActiveRecord::Migration
+    def self.up
+        add_column :public_bodies, :first_letter, :string
+        add_index :public_bodies, :first_letter
+        PublicBody.update_all "first_letter = upper(substr(name, 1, 1))"
+        change_column :public_bodies, :first_letter, :string, :null => false
+    end
+
+    def self.down
+        remove_column :public_bodies, :first_letter
+    end
+end
diff --git a/db/schema.rb b/db/schema.rb
index 193e50378..496df6f0c 100644
--- a/db/schema.rb
+++ b/db/schema.rb
@@ -9,7 +9,7 @@
 #
 # It's strongly recommended to check this file into your version control system.
 
-ActiveRecord::Schema.define(:version => 65) do
+ActiveRecord::Schema.define(:version => 66) do
 
   create_table "acts_as_xapian_jobs", :force => true do |t|
     t.string  "model",    :null => false
@@ -111,8 +111,10 @@ ActiveRecord::Schema.define(:version => 65) do
     t.text     "url_name",                          :null => false
     t.text     "home_page",         :default => "", :null => false
     t.text     "notes",             :default => "", :null => false
+    t.string   "first_letter",                      :null => false
   end
 
+  add_index "public_bodies", ["first_letter"], :name => "index_public_bodies_on_first_letter"
   add_index "public_bodies", ["url_name"], :name => "index_public_bodies_on_url_name", :unique => true
 
   create_table "public_body_tags", :force => true do |t|
diff --git a/todo.txt b/todo.txt
index 658a8533f..6893683b2 100644
--- a/todo.txt
+++ b/todo.txt
@@ -1,4 +1,5 @@
 Gah - fix caching regarding info_request that correspondence part belongs to
+Profile it!
 
 Test data for Tony