Poor Man’s Search Engine
You don’t have to use SQL to solve every problem.
- SQLで速くて正確な検索機能を実現することは、見かけによらず難しい
Objective: Full-Text Search
-
SQLの原則として、カラムはatomicである
- 値と値の比較はできるが、値まるごとの比較である
- 部分文字列の比較は効率や正確さを欠く運命にある
Antipattern: Pattern Matching Predicates
-
パターンマッチング
- LIKE
- REGEXP
-
欠点
-
パフォーマンス低い
- インデックス効かない
-
意図しないマッチ
%one%
がlonely
にマッチしちゃったり
-
-
REGEXPなら単語境界を指定できたりする
- MySQL
SELECT *
FROM Bugs
WHERE description
REGEXP '[[:<:]]one[[:>:]]';
How to Recognize the Antipattern
-
こういうのが聞こえてきたら注意:
-
「LIKE式のワイルドカードの間に変数を挿入するにはどうすればいい?」
- アプリケーションプログラマが思いつくやつ
-
「複数の単語を含んでいて、特定の語は含まず、所定の語の変形も含む文字列を検索するには?」
- 正規表現で解決するのは困難
-
「記事数増えたら遅くて使えなくなった」
- DBの容量が増えると、本アンチパターンのスケーラビリティの乏しさが露見する
-
Legitimate Uses of the Antipattern
-
滅多に発行しないクエリなら良い
- インデックスによる利点が少ない
- 単純な要件なら良い
Solution: Use the Right Tool for the Job
- 標準SQLを使わない
- 標準SQLを使うにしても、部分文字列のマッチングよりも概して効率的な方法がある
Vendor Extensions
- 特定のDB製品依存で良いならば、高パフォーマンスを得るうえで最善の選択
Full-Text Index in MySQL
-
MyISAMストレージエンジン専用でFULLTEXT INDEXをサポート
-
対象のデータ型
- CHAR
- VARCHAR
- TEXT
- MATCH関数で検索
-
Text Indexing in Oracle
-
CONTEXTインデックス
- CONTAINS演算子で検索
-
CTXCATインデックス
- 短いテキスト用
- CATSEARCH演算子で検索
-
CTXXPATHインデックス
- XML検索特化
- existsNode演算子で検索
-
CTXRULEインデックス
- 類別用
Full-Text Search in Microsoft SQL Server
- ストアドプロシージャでfull-textインデックス作成
- CONTAINS演算子で検索
Text Search in PostgreSQL
-
TSVECTORデータ型
- 検索用データ構造
- さらに転値インデックス(GIN index: generalized inverted index)を作成する
Full-Text Search (FTS) in SQLite
- SQLiteの標準的なテーブルは効率的な全文検索をサポートしない
-
検索特化のvirtual table拡張を使う
- FTS1
- FTS2
- FTS3
Third-Party Search Engines
- Sphinx Search
-
Apache Lucene
- Solrサブプロジェクト
- 【補】Elasticsearch
Roll Your Own
-
自前で転値インデックスを作るという選択肢
- プロプライエタリな(独占的な)検索機能を使いたくない場合
- SQLと別途検索エンジン製品をインストールしたくない場合
英語
-
deceptively difficult
- 見かけによらず難しい
-
gulf
- 隔たり