既存テーブルの構造は変えれなくても、もし新規にテーブルの追加がOKなのであれば、空白除去したデータで検索用のテーブルを別に作ったらどうでしょう。
ID | NAME |
---|---|
1 | 日本太郎 |
2 | 日本二郎 |
3 | 日本太郎 |
4 | 日本太郎 |
5 | 日本三郎 |
みたいなBテーブルがあれば、日本太郎のIDだけ取ってこれますよね。
あとは、TRIGGER作ってAテーブル更新時にBテーブルも更新されるようにすれば、なんかいい感じにできるかも?
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で期待する結果を取得することができました。
ご回答、案をいただきありがとうございます!
既存テーブルの構造は変えれなくても、もし新規にテーブルの追加がOKなのであれば、空白除去したデータで検索用のテーブルを別に作ったらどうでしょう。
ID | NAME |
---|---|
1 | 日本太郎 |
2 | 日本二郎 |
3 | 日本太郎 |
4 | 日本太郎 |
5 | 日本三郎 |
みたいなBテーブルがあれば、日本太郎のIDだけ取ってこれますよね。
あとは、TRIGGER作ってAテーブル更新時にBテーブルも更新されるようにすれば、なんかいい感じにできるかも?
@suzu_cat_x
Questioner何件かは不明ですが、40sは遅いです。全件検索と同じ時間でしょうか?
FireBirdは海外ではメジャーな高速DBです。
全文検索のクエリー関数または、拡張モジュールがあるとおもいますよ!
訂正:メッセージアーカイブがv6から対応しているが、メールのみ全文検索が可能の様?
上記はpostgreSQLの全文検索の話です。
近年、wordpressの普及により、DBも全文検索機能を実装したり、redisを中継させる手法がチラホラ登場しています。
@suzu_cat_x
Questioner