SQLite 部分インデックス入門：CREATE INDEX ... WHERE

一部の行だけを対象にする部分インデックス

通常のインデックスはテーブルのすべての行にエントリを持ちますが、SQLite の部分インデックス(partial index)は、作成時に指定した WHERE 句にマッチする行だけをエントリ対象にします。インデックスが小さくなるので、たどるページ数も減り、対象範囲外の行への INSERT や UPDATE でも余計なメンテナンスコストがかかりません。

書き方は通常の CREATE INDEX の末尾に WHERE を付けるだけです:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    total REAL NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX idx_orders_pending
    ON orders(created_at)
    WHERE status = 'pending';

idx_orders_pending には status = 'pending' の行だけが入ります。発送済み・キャンセル・返金済みの注文は一切含まれません。orders テーブルの95%が過去データで、普段は未処理の注文ばかり叩いているなら、同じクエリ速度のままインデックスサイズを20分の1に圧縮できる、というわけです。

プランナが実際に部分インデックスを使ってくれる条件

SQLite の部分インデックスは、クエリが対象行をインデックスのカバー範囲内に絞り込んでいることを SQLite 自身が 証明 できないと使われません。一番手堅いのは、インデックス側の WHERE 句をそのままクエリにも書いてしまう方法です。

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    total REAL NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX idx_orders_pending
    ON orders(created_at)
    WHERE status = 'pending';

INSERT INTO orders(customer_id, status, total, created_at) VALUES
    (1, 'pending',  42.0, '2026-01-10'),
    (2, 'shipped',  99.0, '2026-01-11'),
    (3, 'pending', 120.0, '2026-01-12');

EXPLAIN QUERY PLAN
SELECT id FROM orders
WHERE status = 'pending'
ORDER BY created_at;

プランに USING INDEX idx_orders_pending が出てくればOKです。クエリから status = 'pending' を外すと、プランナはテーブルフルスキャンに逆戻りします。クエリがインデックス対象の範囲内に収まっていることを判断できないからです。

覚えておくべきルールは1つ。クエリの WHERE が、インデックスの WHERE を含意していること。同じカラム・同じ値の等価条件なら間違いありません。不等号や OR が絡むと話がややこしくなるので、EXPLAIN QUERY PLAN で確認しましょう。

なぜ使うのか — 3つのメリット

sqlite 部分インデックスを使う具体的なメリットは次の3つです。

1. ディスク容量を節約できる。 条件にマッチした行だけが格納されます。「テーブル全体の1%だけがホットデータ」というワークロードなら、インデックスのサイズもフルインデックスのおよそ1%で済みます。
2. 書き込みが軽くなる。 INSERT や UPDATE の際、行が条件に合致したときだけインデックスが更新されます。前述のテーブルに status = 'shipped' の行を INSERT しても、idx_orders_pending には一切触れません。
3. 検索速度はそのまま。 B-tree のルックアップはインデックスサイズに対して対数オーダーです。インデックスが小さくなれば検索もわずかに速くなりますが、本当に効いてくるのはその周辺 — キャッシュミスの減少や I/O の削減です。

カラムの値が極端に偏っている — 大半の行が同じ値で、レアな値だけを検索したい — というケースは、まさに部分インデックスの教科書どおりの出番です。

部分ユニークインデックス(これが本命)

普通の UNIQUE 制約はすべての行に適用されます。論理削除を導入した瞬間、これが厄介な問題になります。

-- 失敗: email = 'a@x.com' の行が 2 つあり、片方は削除済みでも重複とみなされる。
CREATE UNIQUE INDEX idx_users_email ON users(email);

SQLite の部分ユニークインデックス（partial unique index）を使うと、本当にユニーク性を担保したい行だけに絞って制約をかけられます。

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    deleted_at TEXT
);

CREATE UNIQUE INDEX idx_users_email_active
    ON users(email)
    WHERE deleted_at IS NULL;

INSERT INTO users(email, deleted_at) VALUES
    ('a@x.com', '2026-01-01'),  -- 論理削除済み
    ('a@x.com', '2026-01-05'),  -- 再度論理削除
    ('a@x.com', NULL);          -- 有効な行は1つのみ、許可される

SELECT email, deleted_at FROM users;

同じメールアドレスの行が3つあるのに制約違反にならないのは、ユニーク判定の対象が deleted_at IS NULL の行だけだからです。試しに同じメールでもう1件「生きている」行を挿入してみてください。SQLite は UNIQUE constraint failed を返します。

このパターンは至る所で登場します。顧客ごとにアクティブなサブスクリプションは1つだけ、ユーザーごとにプライマリ住所は1つだけ、注文ごとに未払いの請求書は1つだけ——こうした要件を素直に表現できるのが、sqlite の部分ユニークインデックス(partial unique index)です。

NULL を除外するインデックスの作り方

NULL とインデックスの関係はちょっと厄介です。よくある要件として「NULL は完全に無視したい」というケースがあります。たとえば external_id カラムがあって、ほとんどの行は NULL だけれど、値が入っている行については一意であってほしい、というような場合です。

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    external_id TEXT
);

CREATE UNIQUE INDEX idx_products_external_id
    ON products(external_id)
    WHERE external_id IS NOT NULL;

INSERT INTO products(name, external_id) VALUES
    ('Widget',  'EXT-001'),
    ('Gadget',  NULL),
    ('Doohickey', NULL),
    ('Sprocket', 'EXT-002');

SELECT name, external_id FROM products;

NULL同士は何個あっても衝突しないので、EXT-001 や EXT-002 の行についてはきちんと一意性が保証されます。さらに、NULLの行はインデックスに格納されないため、インデックス自体のサイズも小さく済みます。テーブルが大きくなっても external_id での検索は高速のままです。

WHERE句で参照できるもの

sqlite 部分インデックスの WHERE 句に書ける内容にはかなり厳しい制約があります。参照できるのは次のものだけです。

• インデックス対象テーブルのカラム
• リテラル(定数)
• 一部の決定的な組み込み関数

逆に、以下は参照できません。

• 他のテーブル
• サブクエリ
• random() や CURRENT_TIMESTAMP のような非決定的な関数
• パラメータや変数

これは理にかなっています。SQLiteは行のINSERTやUPDATEのたびにこのフィルタ条件を評価する必要があり、結果は常に安定していなければならないからです。というわけで、次のような書き方はOKです。

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_type TEXT NOT NULL,
    priority INTEGER NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX idx_events_high_priority
    ON events(created_at)
    WHERE priority >= 5 AND event_type IN ('error', 'critical');

でも WHERE created_at > date('now') はダメです。date('now') は時間とともに値が変わるので、インデックス対象の行がSQLiteの足元でどんどん変わってしまいます。

動作確認のワークフロー

部分インデックスを追加したら、次の3つを順に確認しておきましょう。

CREATE TABLE jobs (
    id INTEGER PRIMARY KEY,
    queue TEXT NOT NULL,
    status TEXT NOT NULL,
    run_at TEXT NOT NULL
);

CREATE INDEX idx_jobs_runnable
    ON jobs(run_at)
    WHERE status = 'queued';

INSERT INTO jobs(queue, status, run_at) VALUES
    ('default', 'queued',  '2026-01-10 10:00'),
    ('default', 'done',    '2026-01-10 09:00'),
    ('email',   'queued',  '2026-01-10 11:00');

-- 1. プランナはこのインデックスを選択するか？
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'queued' ORDER BY run_at LIMIT 10;

-- 2. フィルタの範囲外のクエリは無視するか？
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'done' ORDER BY run_at;

-- 3. フィルタが完全に欠けているクエリはスキップするか？
EXPLAIN QUERY PLAN
SELECT id FROM jobs ORDER BY run_at;

クエリ1では idx_jobs_runnable が使われるはずです。クエリ2と3はテーブルスキャンか、別のインデックスがあればそちらにフォールバックします。想定していないクエリでプランナが部分インデックスを選んだ場合は、フィルタ条件を見直してみてください。思っているより範囲が広いのかもしれません。

部分インデックスを使わないほうがよい場面

部分インデックス(partial index)は鋭い武器ですが、次のような場合は避けましょう。

• フィルタがテーブルの大半に当たる場合。 「アクティブ」な行が全体の90%を占めるなら、部分インデックスは「ひと手間多い普通のインデックス」でしかありません。素直にカラムにインデックスを張りましょう。
• クエリ側がフィルタを文字どおり書いていない場合。 ORM が WHERE status IN (?, ?, ?) を組み立てたり、フィルタを動的に生成していたりすると、プランナがマッチを認識してくれないことがよくあります。思い込みは禁物。EXPLAIN QUERY PLAN で必ず確かめてください。
• 対象となる「ホットな部分集合」が時間とともに変わる場合。 「直近30日の注文」に部分インデックスを張りたい気持ちはわかりますが、フィルタは決定的(deterministic)である必要があるので、これは表現できません。インデックスを張り直すか、スキーマを変えるか(recent_orders テーブルを別に持つ、夜間バッチでフラグを切り替える archived カラムを用意する、など)の対応が必要です。

フィルタが安定していて、大きなテーブルのごく一部にしか当たらない —— そんなときの部分インデックスは、SQLite で打てるチューニングの中でも特にコスパの高い一手です。

次回: クエリプランの読み方

このページではインデックスが実際に使われているかを確認するために、何度も EXPLAIN QUERY PLAN に頼ってきました。このツールは1ページ丸ごと割く価値があります。出力の読み方、各キーワードの意味、そして「気持ちのいいインデックスルックアップ」と「こっそり混ざったフルスキャン」をどう見分けるか —— 次回はそこを掘り下げていきます。