TEXT型のフィールドにインデックスを張るときの注意

フィールドに保存した文字列を検索しても該当しない、という現象にはまりました。（はまりレベル３）

テーブルHBにテキスト型のフィールドを追加しました。そのフィールドにHBのすべてのレコードについて、メソッドで求めた文字列を保存しました。その保存した文字列で検索しても該当しない、のです。

１対Ｎ構造のテーブルがあって（テーブルHBとテーブルKJ）、Ｎ側（テーブルKJ)のラインナップが同じであるテーブルHBのレコードを探す、という機能を実装していたときのことです。ロジック的には、テーブル HBの各レコードについてテーブル KJをクエリして判定すればいいのですが、テーブルHBにはレコードが１万件以上あって、遅くてとても実用になりません。テーブルKJをキャッシュして配列に持たせても同様に遅い、という状況でした。

処理の高速化を図るため、テーブルHBに「HB_KJLINE」というフィールドを用意して、KJ_IDをコンマ区切りの文字列で連結して保存します。こうしておくと上記のクエリはテーブルHBだけをクエリすればよいことになります。検索時に遅いので保存時に一つ余計なフィールドを用意して検索のための値を保存しておく、というデータベース高速化の常套手段です。

高速化のためのコーディングですから、追加したHB_KJLINEにはインデックスを作成しました。HB_KJLINEの文字列は255文字を超えるでしょうからテキスト型です。そして実行、値はHB_KJLINEに入っていますが、あるはずの文字列で検索しても該当なし、です。

カレントテーブル表示（コマンド＋Uキー）でクエリ（コマンド＋Y）して、そのフィールドに入っている文字列で検索してみても、該当しません。一つレコードを保存してみると、保存したレコードだけ正常に該当します。

（4D Japanのサポートに電話して解決しました。）

原因はインデックスにありました。テキスト型のフィールドにインデックスを作成した場合、最初の1,024文字までしか結果が保証されない、という4Dの仕様です。HBには380個以上のKJを持つものがあり、この場合KJ_IDがコンマを入れて一つ４〜５文字とするとKJLINEの文字数は1,024文字を超えます。インデックスのチェックを外したら検索できるようになりました。

教訓１：

テキスト型のフィールドにはインデックスを作成しない。高速化を図る場合は別の方法を考えること。

ここで次の教訓も覚えておきましょう。

教訓2：

4Dでは、レコード数が32000件以上の場合、FInd in Arrayの該当レコード番号が保証されなくなる。（はまりレベル５）