インデックスを使って検索する方法。
成果物
情報源
方法
like
やglob
はbetween
のようにインデックスを使って範囲検索できる。だが、それは限らた条件のときだけ。詳細は上記参照。注目すべきは以下。
- パターンがワイルドカードで始まらないこと(前方一致のときのみインデックスを使える)
glob
演算子の場合、組込のbinary
照合シーケンスを使用して列にインデックスを付ける必要があるlike
演算子の場合
やってみる
最初に検索の要件を考える必要がある。たとえば大文字・小文字の区別をしない検索だけを要件にするなら以下。
create table T(A text collate nocase); create index T_A on T(A); insert into T values('1A'); insert into T values('11A'); insert into T values('11A1'); insert into T values('2A'); insert into T values('2B'); pragma case_sensitive_like = fasel; select A from T where A like '_A';
もし区別するパターンでも高速にしたいなら、そのインデックスを作成することになる。ただし、ほぼ同じデータが量産されることになる。必要なディスク容量が増えてしまう。よってそれは省略する。
検索方法はlike
句を使う。glob
は使わない。区別する検索しかできないためインデックスが使えないから。
区別しないのでcollate
をnocase
にする。また、like
句も区別しないよう以下のように設定する(デフォルト値だが念の為)。
PRAGMA case_sensitive_like = false;
インデックスが使われているか確認する。前方一致のときのみ使える。
explain query plan select A from T where A like 'A_';
QUERY PLAN
`--SEARCH TABLE T USING COVERING INDEX T_A (A>? AND A<?)
もちろん完全一致でも使える。だがlike
句を使う意味がない。=
比較と同じ。
explain query plan select A from T where A like 'A';
QUERY PLAN
`--SEARCH TABLE T USING COVERING INDEX T_A (A>? AND A<?)
だが、後方一致、部分一致のときはインデックスが使われない。全表スキャンになってしまう。
explain query plan select A from T where A like '_A';
QUERY PLAN
`--SCAN TABLE T
explain query plan select A from T where A like '_A_';
QUERY PLAN
`--SCAN TABLE T
なお、glob
はインデックスが一切使われない。インデックスのソート順がnocase
に準じているから。glob
はbinary
順しかサポートされておらず、その順に並んでいないインデックスは役に立たないため。
explain query plan select A from T where A glob 'A?';
QUERY PLAN
`--SEARCH TABLE T USING COVERING INDEX T_A (A>? AND A<?)
explain query plan select A from T where A glob '?A';
QUERY PLAN
`--SCAN TABLE T
explain query plan select A from T where A glob '?A?';
QUERY PLAN
`--SEARCH TABLE T USING COVERING INDEX T_A (A>? AND A<?)
ところで、どう考えても最も使うのは「部分一致」だろう。それなのにインデックスが使えない。これは痛い。like
やglob
のためにインデックスを作っても費用対効果は低いかもしれない。どうせ遅いならもっと汎用性のあるregexp
か、キーワードを増やすFTSでも使ったほうが良いかも?
対象環境
- Raspbierry pi 3 Model B+
- Raspbian stretch 9.0 2018-11-13
- bash 4.4.12(1)-release
- SQLite 3.29.0
- MeCab 0.996ユーザ辞書
$ uname -a Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux
前回まで
- SQLite3学習 俯瞰まとめ
- SQLite3学習 環境構築まとめ
- SQLite3学習 インタフェースまとめ(C言語、CLI、対話モード、Tcl...)
- SQLite3学習 ドットコマンドまとめ
- SQLite3学習 JSON拡張まとめ
- SQLite3学習 FTSまとめ(ICU, MeCab)
- SQLite3学習 再帰クエリ(WITH RECURSIVE)
- SQLite3学習 R-Treeモジュール
- SQLite3学習 Geopoly(2次元ベクタ画像の生成)
- SQLite3学習 拡張関数(generate_series)
- SQLite3学習 拡張ライブラリ数学関数(extension-functions.c)
- SQLite3学習 謎と名前
- SQL文の分類(DDL,DML,TCL,DCL)
- SQL構文 alter(rename)
- SQL構文 alter(add column)概要
- SQL構文 alter(add column)制約
- SQL構文 alter(add column)sqlite_master変更しても反映されない
- SQL構文 alter(add column)スキーマ再定義(テーブル再作成による定義変更)
- SQL構文 analyze
- SQL構文 attach/detach
- SQLite3構文 begin,end,commit,rollback,savepoint(deferred,immediate,exclusive)
- SQLite3構文 コメント
- SQLite3構文 create/drop
- SQLite3構文 index(create/drop)
- SQLite3構文 table(create/drop)
- SQLite3構文 列制約(default)
- SQLite3構文 列制約(collate)
- SQLite3構文 列制約(primary key)
- SQLite3構文 列制約(primary key)ベストプラクティス
- SQLite3構文 列制約(unique)
- SQLite3構文 列制約(not null)
- SQLite3構文 列制約(check)
- SQLite3構文 列制約(foreign key references)
- SQLite3構文 表制約(primary key, unique, check, foreign key)
- SQLite3でメタデータを取得する方法(DB名(スキーマ名)、テーブル名、列名、制約)
- SQLite3でTEMPの保存先を指定する
- SQLite3構文 delete
- SQLite3ビルド失敗(SQLITE_ENABLE_UPDATE_DELETE_LIMIT)
- SQLite3をソースからビルドする(SQLITE_ENABLE_UPDATE_DELETE_LIMIT)
- SQLite3構文 delete(limit offset, order by)
- SQLite3クエリプランニング(インデックスの働き)
- SQLite3構文 explain
- SQLite構文 expression
- SQLite構文 expression(リテラル)
- SQLite構文 expression(パラメータ)
- SQLite構文 expression(演算子)
- SQLite構文 expression(in 句)
- SQLite構文 expression(like 句)
- SQLite構文 expression(glob 句)