suzu_cat_x
@suzu_cat_x

Are you sure you want to delete the question?

Leaving a resolved question undeleted may help others!

FireBirdの検索高速化

使用しているバージョン

Firebird 3.0

やりたいこと

氏名(日本 太郎)のように登録されているテーブルAから
ユーザーによる検索条件(日本太郎、日本 太郎、日本 太郎)を抽出するSQLを作成したい
その際、テーブルAや検索条件に含まれているスペースを考慮しないように改修しヒット件数を増やしたい
※テーブルA及び検索条件の氏名の姓名間はスペースなし、半角スペース、全角スペースいずれも考えられる

課題点

思い当たる方法を使用したが、検索時間が長くなってしまうので高速化を行いたい

現在

LIKE句を使用(検索文字列後方のみ曖昧)

 WHERE A.NAME LIKE '日本 太郎%'

→日本太郎、日本 太郎で検索した場合はヒットしない
→検索時間は平均0.06s

試したこと

①REPLACE

WHERE REPLACE(REPLACE(A.NAME , ' ', ''),' ','') LIKE '日本太郎%'

※LIKE後の文字列は呼び出し元プログラムでREPLACE
→検索時間が平均40sになってしまった

②SIMILAR TOを使用した正規表現

WHERE A.NAME SIMILAR TO '( | )?日( | )?本( | )?太( | )?郎%'
WHERE A.NAME SIMILAR TO '日([[:SPACE:]]| )?本([[:SPACE:]]| )?太([[:SPACE:]]| )?郎%'

→検索時間が平均11s

③LIKE句の条件拡張

WHERE A.NAME LIKE '%日%本%太%郎%'

→検索時間が平均9s
スペース以外も引っ張ってしまい検索結果が意図しないものになる可能性がある

質問内容(条件)

既に運用されており、テーブルの構造を変えるのは厳しいと言われておりますので、
Aテーブルに手を入れず、
試したこと以外でデータ量の多いテーブルAから高速でやりたいことを叶える記述方法の案があれば教えて欲しいです
また、外国人等、A.NAMEには複数のスペースが含まれる場合があります

マルチポストについて

当質問は前日にteratailにも投稿させていただいております。
理由:FireBirdがそこまでメジャーなDBではないため、広い範囲で有識者の方に見つけていただくため
片方で解決した際は可能な限り迅速に共有及び質問のクローズをさせていただきますので、何卒ご理解の程よろしくお願い致します。
teratail:https://teratail.com/questions/83edvco240m489

解決方法共有

 WHERE A.NAME LIKE '日%' AND  A.NAME SIMILAR TO '日( | )?本( | )?太( | )?郎%'
 WHERE A.NAME LIKE '日%' AND REPLACE(REPLACE(A.NAME , ' ', ''),' ','') LIKE '日本太郎%'

taratailで案をいただき
上記のように先頭1文字で絞り込みの後にチェックを行った場合に0.09sで期待する結果を取得することができました。
ご回答、案をいただきありがとうございます!

1

2Answer

既存テーブルの構造は変えれなくても、もし新規にテーブルの追加がOKなのであれば、空白除去したデータで検索用のテーブルを別に作ったらどうでしょう。

ID NAME
1 日本太郎
2 日本二郎
3 日本太郎
4 日本太郎
5 日本三郎

みたいなBテーブルがあれば、日本太郎のIDだけ取ってこれますよね。
あとは、TRIGGER作ってAテーブル更新時にBテーブルも更新されるようにすれば、なんかいい感じにできるかも?

1Like

Comments

  1. @suzu_cat_x

    Questioner

    ありがとうございます!
    新規テーブルの追加は最終手段で考えています。
    まずはSQLでいいアイデアがあればと質問させていただきました。
    テーブルの追加や変更で対応するのが理想ですよね…
  2. 複雑なSQLを組んだところで、恐らくINDEXがあまり効果的に機能しないので、素直に別テーブル増やすのがいいかと思います。FireBird以外のDBでも使える方法ですし。

何件かは不明ですが、40sは遅いです。全件検索と同じ時間でしょうか?

FireBirdは海外ではメジャーな高速DBです。

全文検索のクエリー関数または、拡張モジュールがあるとおもいますよ!

訂正:メッセージアーカイブがv6から対応しているが、メールのみ全文検索が可能の様?

上記はpostgreSQLの全文検索の話です。
近年、wordpressの普及により、DBも全文検索機能を実装したり、redisを中継させる手法がチラホラ登場しています。

1Like

Comments

  1. @suzu_cat_x

    Questioner

    ありがとうございます、件数はとても多いです。

    今回は別の方法で解決させていただきました。
    全文検索のクエリー関数、拡張モジュールについて知識がなかったため勉強してみたいと思います!

Your answer might help someone💌