例として下記のようなテーブルがあるとします。
productsテーブル
id | product_name | category | price | detail
ユーザがWEBから検索ワードを指定し、そのワードが含まれるレコードを抽出したいです。
ただ、何も考えずにSQLを作成すると下記のように、非常にハイコストなSQLができてしまいます。
(AND検索を想定しています)
＊検索ワードとして"おもちゃ","ハリーポッター"の2語が指定された場合
SELECT id FROM products
WHERE product_name LIKE '%おもちゃ%'
OR category LIKE '%おもちゃ%'
OR price LIKE '%おもちゃ%'
OR detail LIKE '%おもちゃ%'
AND product_name LIKE '%ハリーポッター%'
OR category LIKE '%ハリーポッター%'
OR price LIKE '%ハリーポッター%'
OR detail LIKE '%ハリーポッター%'
今回の例のようなテーブルでは各カラムに文章が入るわけではないため、
ludia等の全文検索を利用するのは筋違いなのでは？と思っています。
こういったパターンにおいてどのような処理を行うのがよいか
ご自身の経験上、ある程度自信をお持ちの方にお伺いできればと思います。
よろしくお願いします。
71
64
コメント（4件)
どこかしらに影響がでるのでUI（ユーザーインターフェース）まで含めた、
システム全体に渡るトータル的なパフォーマンスを検討される事をお勧めします。
（１）項目のデータ特性に応じてUIを変える
price は金額だから「指定なし」「最低額のみ」「最高額のみ」、
「最低額・最高額」の４種類に対応するようにするといった具合に、
まずは、あいまい検索から除外する項目をピックアップ。
category に関しては、プルダウンやリストボックスなどで表示して、
選択させるようにできないかを検討したり、一度に入力してもらうのではなく、
カテゴリーを絞りこんでから、次の項目を入れるような仕組みを作るなどで、
対応できないかを検討。
あいまい検索に対しても、前方一致であればLIKEでも結構高速に動作するので、
検索方法を指定するようにしても良いかもしれない。
（「結構高速」という言葉自体があいまいだったりするのだが、パフォーマンスは、
データによるため一概に比較することが出来ず、あいまいな表現しか出来ない。）
（２）LIKEで済ますか、全文検索を用いるかを検討する
product_nameやdetailのように、あいまい検索する必要があるものに関しては、
１件に含まれるテキストの長さやレコード件数によって検討する必要があるけれど、
どこで線引きすれば良いと言う指標的なものは無い。
データ件数が１万件などと少ない状態であれば、どちらを使っても大差ないと思われるが、
速度面などで不安になるほど大量のデータを相手にするとなれば、
カラムにどのような内容が入るかを詳細に詰めてテストデータを作成し、
実際にテストしてみるという事になる。
以上、直接的回答ではないのでコメント欄にて・・・。
今までこういったケースではご提案いただいたようにプルダウン等を利用し
項目をわけるといった方法で対応していました。
ただ、今回はどうしてもひとつにまとめたいという願望？があり
質問させていただいた次第です。
私がこういったケースには使わないのでは？と思っていた全文検索ですが
試しにludiaをインストールし、簡単なテストを行ってみました。
内容としては日本郵便の配布している12万件程度の郵便番号データを利用し
今回例に挙げさせていただいたようなSQL(ちょっとSQL間違ってましたね･･･)と
Ludiaを用いた場合とでEXPLAIN ANALYZEを行い、実際にかかった処理時間を比較するというものです。
結果としては、明らかに全文検索を用いた方がパフォーマンスがよかったです。
ですので今回は全文検索を使う方向で進めていきたいと思います。
まあ、これはこれで新たな疑問が生まれてしまったわけですが･･･(´д｀;)
ご協力ありがとうございました。
郵便番号辞書は12万件というデータ量を持つため、
一見するとパフォーマンステスト用として適切なように思えるけれど、
（１）町名で括るだけで数分の１になってしまうほど単純明快な構造
（２）インポートする時点でソートされている
というような事で、全文検索用のインデックスとしては、
かなり効率の良いものが出来上がっている可能性が高く、
比較テスト用としては不適切かもしれない。
このコメントだけを見た人からすれば「では、どうすれば良いのか？」
という反論が出てきそうではあるのですが、具体的にどのような分野の、
どのようなデータを扱いたいかについては明示できない状況のようですから、
回答も無理であるという事は理解して欲しい。
（なぜ、言い訳してるのかって？ それは・・・ナイショ）
【４】データ追加・更新・削除時の動作コスト増加
データ更新が頻繁に行われるテーブルの場合は、
全文検索用のインデックス更新にかかる時間も、
無視できなくなる可能性がありますね。
結局のところ、こちらの比較テストも必要という事になりますが、
予測される実データに即した形でテストデータを用意するという所に、
話は戻ってしまいます。
「新たな疑問」に相当するかどうかは判りませんが以上、追記。
やはりテストは本番で使用するものにできるだけ近いものがいいんでしょうね・・・
今回やっていることは仕事でもなく、めんどくさかったので横着してしましましたｗ
本番用のテーブルでは2つのテーブルがあり、その2つを結合して対象データを抽出する
予定なのですが、この場合テーブルのインデックス(Ludiaの配列インデックスが最適なのかなと思っています)
をどうはればよいか迷ってしまったんですね～
そもそも、この場合に複数テーブルにまたがってインデックスをはれるか調べていないですが・・・
とりあえず試してみようと思います。
ありがとうございました。