理解できない現象があったので、記録してみます。

下は、like検索でインデックスが使用されるための条件としていくつか挙げられている条件です。

2. The right-hand side of the LIKE or GLOB must be a string literal that does not begin with a wildcard character.

LIKEかGLOBの右側は、ワイルドカードで始まらないリテラル文字列でなければならない。これは、like検索の対象のカラムにインデックスが作成されていても、最初の文字がわからなければ、ソートされたインデックスを効果的に検索することは、できなさそうなので、理解できる気がしました。

だとすると、下のような使い方では、インデックスは使われるわけがない。

select  t.id
from ticket as t
inner join message as m on m.id = t.last_message_id
where  (m.field3 like '%hogehoge%')
group by t.id
order by t.registerdate desc

今まで、like検索でもインデックスを使ってもらおうとしていたので、インデックスを貼っていました。

CREATE INDEX index_message_2 on message (ticket_id, field2, registerdate);

テーブル定義の全体はここ

その状態で、妙にlike検索の応答速度が遅い状態でした。(like条件が複数になると、10秒以上かかってました) インデックスの貼り方が悪いのかと思い試行錯誤していたところ、インデックスを外したときに速い(1秒以内)ことがわかりましたorz.

結局、like検索を行なう対象のカラムにインデックスが貼ってあると、逆効果になることがあるということでしょうか。どちらにしても、発行しているsql文ではインデックスは、使われない仕様なんだから、無視されるだけなら理解できるんですが、逆効果になるのが、なんとも解せないかんじです。

#これ以上突っこむには、sqliteのソースを見ないとかな。でもstarbug1を進めたいので放っとく。