ActiveScaffold::search が遅い

ActiveScaffold を覚えたので、調子に乗って自宅鯖にある某ラジオ局の ON AIR 履歴一覧を Rails で書き直してみた。

2001 年から蓄積した 84 万曲の履歴から、最新のものを一覧表示する。5 分更新。

ここで ActiveScaffold の Search アクションを使って検索ボックスを実装したのだが、前方一致検索にしたのに数秒単位で時間がかかる。
データベースには index を張ってあるのになぜ？　と思い、Rails のログからクエリを取り出してみると、

SELECT count(*) AS count_all
       FROM `songs`
       WHERE ((LOWER(songs.`artist`) LIKE 'kotoko%'
            OR LOWER(songs.`title`) LIKE 'kotoko%'))

大小文字に関係なくマッチさせるために、条件の左辺に LOWER() を使っている。これを DESCRIBE すると、

mysql> DESCRIBE SELECT count(*) AS count_all FROM `songs`
       WHERE ((LOWER(songs.`artist`) LIKE 'kotoko%' OR LOWER(songs.`title`) LIKE 'kotoko%'))\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: songs
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 840665
        Extra: Using where
1 row in set (0.00 sec)

ということでインデックスが効かず、84 万件を全サーチしていたのだった。試しに左辺に LOWER() をかけずに実行すると、

mysql> DESCRIBE SELECT count(*) AS count_all FROM `songs`
       WHERE ((songs.`artist` LIKE 'kotoko%' OR songs.`title` LIKE 'kotoko%'))\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: songs
         type: index_merge
possible_keys: title,artist
          key: artist,title
      key_len: 62,62
          ref: NULL
         rows: 233
        Extra: Using sort_union(artist,title); Using where
1 row in set (0.00 sec)

このようにインデックスが効く。MySQL の大小文字同一視検索は列の collation が *_ci なら有効なので、LOWER() を使う必要はないはず。

というわけで ActiveScaffold にパッチする。grep したところ Search アクションのクエリを生成しているのは ActiveScaffold::Finder なので、RAILS_ROOT/lib/ に active_scaffold_hooks.rb と称して以下のファイルを作成。

module ActiveScaffold
  module Finder
    def self.create_conditions_for_columns(tokens, columns, like_pattern = '%?%')
      # if there aren't any columns, then just return a nil condition
      return unless columns.length > 0
      
      tokens = [tokens] if tokens.is_a? String
      
      where_clauses = []
      columns.each do |column|
        where_clauses << "#{column.search_sql} LIKE ?"
      end
      phrase = "(#{where_clauses.join(' OR ')})"

      sql = ([phrase] * tokens.length).join(' AND ')
      tokens = tokens.collect{ |value| [like_pattern.sub('?', value.downcase)] * where_clauses.length }.flatten

      [sql, *tokens]

    end

    def self.condition_for_column(column, value, like_pattern = '%?%')
      return unless column and column.search_sql and value and not value.empty?
      case column.form_ui || column.column.type
        when :boolean, :checkbox
        ["#{column.search_sql} = ?", (value.to_i == 1)]

        when :integer
        ["#{column.search_sql} = ?", value.to_i]

        else
        ["#{column.search_sql} LIKE ?", like_pattern.sub('?', value.downcase)]
      end
    end
  end
end

中身は本来の ActiveScaffold::Finder のコピペ + LIKE 左辺の LOWER() の除去。こいつをenvironment.rb で require すればおk.

修正 2008.04.04: フックを environment.rb から require するように変更。