SQLite3 のインデックスと検索速度

Rails + SQLite3 を使って、 DB のレコードを検索する場合の速度を測った。 調べたのは、インデックスを張る前後でどれくらい速度が変わるかということ。

インデックスを張らない場合

まず、普通にモデルを作り、インデックスを張らない状態で検索する。

• データは10万件程度。
• 検索回数は100回。
• 検索は完全一致と前方一致 (LIKE 演算) の2種類。
• 実行環境は VMware 上の Debian sarge 。 SQLite は 3.2.1 。

前方一致検索のために、 search_by_name メソッドを作成している。

class Item
  def self.search_by_name(name)
    if name.blank?
      []
    else
      find(:all, :conditions => ["name like ?", name + '%'])
    end
  end
end

時間の計測には、 Rails の benchmarker を使った。

$ script/performance/benchmarker 100 'Item.find_by_name(Item.find(:first).name)'
            user     system      total        real
#1      8.910000   6.420000  15.330000 ( 15.448537)
$ script/performance/benchmarker 100 'Item.search_by_name(Item.find(:first).name)'
            user     system      total        real
#1     10.730000   6.520000  17.250000 ( 39.949587)

結果、完全一致で15秒、前方一致で40秒だった。

インデックスを張った場合

次に、検索対象のレコードにインデックスを張る。 マイグレートファイルを以下のように作る。

class AddItemsNameIndex < ActiveRecord::Migration
  def self.up
    add_index :Items, :name
  end

  def self.down
    remove_index :Items, :name
  end
end

マイグレートを実行し、インデックスを張る。

$ rake migrate

完全一致と前方一致でもう一度計測する。

$ script/performance/benchmarker 100 'Item.find_by_name(Item.find(:first).name)'
            user     system      total        real
#1      0.030000   0.140000   0.170000 (  0.164136)
$ script/performance/benchmarker 100 'Item.search_by_name(Item.find(:first).name)'
            user     system      total        real
#1     11.320000   6.780000  18.100000 ( 36.684580)

完全一致は0.16秒と、約1/100になった。 一方、前方一致の場合は36秒と、ほとんど変わらない。

とりあえずのまとめ

こんな結果になった。

調べてみると、sqliteのLIKE演算でインデックスを使う方法 に情報があった。 「LIKE ‘/foo/bar/%‘のように%が最後 」の LIKE 演算には、デフォルトではインデックスは使われないらしい。 コンパイル時に -DSQLITE_CASE_SENSITIVE_LIKE を指定すればいいらしいけど、実行時に指定する方法は無いのかな。 The SQLite Query Optimizer Overviewを見ると、 PRAGMA case_sensitive_like=ON; とすればいいらしいけど、どこでやればいいか分からない（普通に実行しても結果は変わらなかった）。