DBのインデックスの長さとは

Question

Rails5.0, HerokuのPOstgreを使用しています。
Railsアプリで、DBのtext型のカラムにindexを貼りたいと思っていますが、内容が長いためエラーが出るという問題に直面しています。前提として、

• text型にもindexを貼ることができる
• 長いテキストにはエラーが出る？

ということはわかっており、調べた結果インデックスの長さを length: という形で指定できることを知りました。
この長さとは、具体的にどういう処理が施されるのでしょうか。ユニーク制約を貼りたいので、被ってしまっては困るのですが、、

Answer 1

PostgreSQLのバージョンによって長さは違うのですが、長いtext型にはindexは作れません。
MD5でindexを作って代用します。

CREATE UNIQUE INDEX idx01 ON table01 (MD5(col01));

値を検索したいのであれば、全文検索が必要です。

Comments

[namenamenameko]

みなさまありがとうございます。
ちょうど自分で調べてshoko1様と同じ方法で実装しました。
もしよろしければ教えていただきたいのですが、
Postgresqlのmd5関数に食わせるtextの長さはいかなる長さでも良いと思われますか？例えば日本語で数千字のものが想定されます、、あと変換によるオーバーヘッドなどが生じないかとも思っています。

[shoko1]

文字の長さより更新頻度が問題になってくると思います。しかし、ユニーク制約が必要なのであればシステムとして入力値のチェックが必須で、UNIQUEインデックスよりコストの低い手段はほとんどないと思われます。

[namenamenameko]

ありがとうございます。

[shoko1]

あと、長さについての質問で、md5の衝突を気にされているのであれば、レコード件数が問題になりますが、通常の件数（例えば1億件程度）では問題となりません。

Answer 2

※ すみません、タグをみてMySQLの場合の回答をしてしまいましたが、Postgresの場合は違う部分もあると思います

MySQLのインデックスに長さを指定すると、先頭からその文字数だけがインデックスの対象となります。

URLということで、latin1にすれば767バイトまではVARCHAR(767)とすることで完全にインデックスを張れます。

TEXTに完全なUNIQUE INDEXは作れませんので、プログラム的にカバーするしかありません。

Comments

[namenamenameko]

先頭からなのですね、ありがとうございます。