実行できるSQLiteドキュメント: インデックス

Q: SQLite でインデックスを作るには？

基本は CREATE INDEX indexname ON tablename(columnname); です。一意制約を付けたいときは CREATE UNIQUE INDEX を使います。インデックス名はテーブル単位ではなくデータベース全体で一意でなければならない点に注意してください。削除は DROP INDEX indexname; でOKです。

Q: どんなときにインデックスを張るべき？

WHERE でよく絞り込む列、JOIN のキー、ORDER BY に使う列が候補です。とくにテーブルが大きく、クエリで取得する行数が全体のごく一部、というケースで効果が出ます。一方で、何でもかんでも張ってしまうと INSERT / UPDATE / DELETE が遅くなり、ディスクも食います。張ったあとは必ず EXPLAIN QUERY PLAN でプランナが本当にそのインデックスを使っているか確認しましょう。

Q: なぜ作ったインデックスが使われないの？

よくある原因はこのあたりです。テーブルが小さくフルスキャンの方が安いと判断された、列を関数で包んでいる（WHERE lower(email) = ... だと email のインデックスは効きません）、OR でインデックスのない列とつないでいる、統計情報が古い、など。ANALYZE で統計を更新し、EXPLAIN QUERY PLAN でプランナの選択を確認してみてください。

Q: テーブルに張ってあるインデックスを一覧で見るには？

特定テーブルだけ見るなら PRAGMA indexlist('tablename');。データベース全体なら sqlitemaster を直接たたいて SELECT name, sql FROM sqlitemaster WHERE type = 'index'; でOKです。sqliteautoindex* という名前のものは、PRIMARY KEY や UNIQUE 制約のために SQLite が自動で作ったインデックスです。

インデックスとは何か

インデックスとは、テーブルとは別に作られるデータ構造（ソート済みの B-tree）で、SQLite がテーブル全体を走査せずに特定のカラム値で行を見つけられるようにするものです。インデックスがないと、WHERE email = 'rosa@example.com' のようなクエリは全行を読み込んで一つずつ突き合わせます。email にインデックスを張れば、SQLite は B-tree をだいたい log(n) ステップでたどり、一致する行へ一直線に飛べます。

ただし、この高速化はタダではありません。インデックスは対象カラムの値と、元の行へのポインタをコピーして持つ構造です。そのため INSERT、インデックス対象カラムの UPDATE、DELETE のたびにインデックス側も更新が必要になります。ディスク使用量は増え、書き込みスループットは少し落ちます。要は「書き込みでコストを払い、読み込みで何倍にもなって返ってくる」というトレードオフです。

sqlite create index でインデックスを作る

基本構文はこうです。

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

INSERT INTO users (email, name, created_at) VALUES
    ('rosa@example.com', 'Rosa', '2026-01-10'),
    ('boris@example.com', 'Boris', '2026-02-14'),
    ('ada@example.com', 'Ada', '2026-03-01');

CREATE INDEX idx_users_email ON users(email);

SELECT * FROM users WHERE email = 'boris@example.com';

命名規則について。多くのチームでは idx_<テーブル名>_<カラム名> という形式を使っていて、これならインデックスの用途がひと目で分かります。インデックス名はテーブル単位ではなくデータベース全体で一意である必要があるので、名前にテーブル名を含めておくわけです。

削除するときはこちら:

DROP INDEX idx_users_email;

Indexesはあくまでパフォーマンスを支える土台です。インデックスを削除してもデータ自体には何の影響もなく、変わるのはクエリの速度だけです。

SQLite のユニークインデックス

ユニークインデックス(unique index)は一石二鳥の存在です。検索を高速化するだけでなく、同じ値を持つ行が複数存在しないことを保証してくれます。

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_accounts_username ON accounts(username);

INSERT INTO accounts (username) VALUES ('rosa');
INSERT INTO accounts (username) VALUES ('boris');
INSERT INTO accounts (username) VALUES ('rosa');

3 つ目の INSERT は UNIQUE constraint failed: accounts.username で失敗します。SQLite は PRIMARY KEY と UNIQUE カラムに対して自動的にユニークインデックスを作成するので、sqlite_autoindex_<table>_<n> という名前で一覧に出てきます。テーブル定義側で制約を宣言していない場合に限り、自分で CREATE UNIQUE INDEX を書けば OK です。

クエリプランナーの実際の挙動

インデックスを作ったからといって、SQLite が必ず使ってくれるわけではありません。クエリプランナーはクエリごとに戦略を選ぶので、何が選ばれたかは EXPLAIN QUERY PLAN で確認できます。

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

INSERT INTO orders (customer_id, total, status) VALUES
    (1, 99.0, 'shipped'), (2, 45.5, 'pending'),
    (1, 12.0, 'shipped'), (3, 200.0, 'pending');

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;

出力に SEARCH ... USING INDEX idx_orders_customer という行があれば、インデックスがちゃんと使われているサインです。一方で SCAN orders と出ている場合は、プランナが「フルスキャンの方が速い」と判断したか(小さなテーブルではよくあります)、クエリの書き方のせいでインデックスが使えなかったかのどちらかです。実行計画の読み方については、後の章でじっくり解説します。

sqlite インデックスが効かないケース

インデックスには、いわゆる「効かない落とし穴」がいくつかあります。以下のパターンは、どれも email に張ったインデックスを台無しにしてしまいます。

-- 関数がカラムをラップしている
SELECT * FROM users WHERE lower(email) = 'rosa@example.com';

-- LIKE の先頭にワイルドカード
SELECT * FROM users WHERE email LIKE '%@example.com';

-- 型の不一致により変換が発生する
SELECT * FROM users WHERE email = 12345;

B-tree は email の生の値でソートされているため、クエリ時にカラムを変換するような処理が入ると、その時点でフルスキャンになってしまいます。対処法はいくつかあります。あらかじめ正規化した値を保存しておく(email_lower カラムを用意する)、式インデックス を使う(CREATE INDEX idx ON users(lower(email)))、あるいは部分一致検索なら SQLite の全文検索(FTS)を使う、といった選択肢です。

カバリングインデックス

クエリが必要とするカラムをすべてインデックスが持っていれば、SQLite はテーブル本体に一切アクセスせずにクエリを処理できます。これがいわゆる カバリングインデックス です。コツは、インデックス定義に必要なカラムを追加で含めておくことです。

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    type TEXT,
    created_at TEXT
);

INSERT INTO events (user_id, type, created_at) VALUES
    (1, 'login', '2026-01-01'),
    (1, 'click', '2026-01-02'),
    (2, 'login', '2026-01-03');

CREATE INDEX idx_events_user_type ON events(user_id, type);

EXPLAIN QUERY PLAN
SELECT user_id, type FROM events WHERE user_id = 1;

クエリが必要としている列が両方ともインデックス内に収まっているので、SQLite は USING COVERING INDEX と表示します。テーブル本体を読みに行かなくて済むわけです。カバリングインデックスは、頻繁に叩かれる読み取りパスに対して最も効果が大きい最適化のひとつ。代償としてインデックスのサイズは膨らみます。複数列インデックスについてはそれ自体が大きなテーマなので、次のドキュメントで詳しく扱います。

sqlite インデックス一覧の確認方法

確認する方法は主に 2 つあります。

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku TEXT UNIQUE,
    name TEXT
);

CREATE INDEX idx_products_name ON products(name);

SELECT name, tbl_name, sql FROM sqlite_master WHERE type = 'index';

これでデータベース内のすべてのインデックスとその CREATE 文が表示されます。特定のテーブルだけ見たいときは PRAGMA index_list('products'); でそのテーブルのインデックス一覧、PRAGMA index_info('idx_products_name'); で各インデックスがどのカラムを対象にしているかを確認できます。sqlite_autoindex_ で始まるものは PRIMARY KEY や UNIQUE 制約から自動生成されたインデックスなので、削除できません。

インデックスを張らないほうがよいケース

逆に、インデックスを追加することで状況が悪化するパターンもいくつかあります。

小さいテーブル。数百行程度ならフルスキャンでもマイクロ秒で終わります。プランナーもインデックスを使ってこない可能性が高く、書き込みコストだけが増えて損です。
書き込みが多くてほとんど検索しないカラム。書き込みのたびに全インデックスが更新されます。ほぼ絞り込みに使わないカラムにインデックスを張るのは、コストだけ払って何も得られない状態です。
カーディナリティが低いカラム単体。値が3種類しかない status カラムにインデックスを張っても、ほとんど絞り込めません。複合インデックスの2番目のカラムとして使ったり、部分インデックス(partial index)にする使い道はありますが、単体ではたいてい元が取れません。
すでにカバーされているケース。(a, b) のインデックスがあれば、(a) 単体のインデックスは不要です。SQLiteは複合インデックスの先頭カラムを、a だけで絞り込むクエリにも使ってくれます。

「このインデックス、張るべき?」への正直な答えはほぼ毎回同じです。とりあえず張ってみて、EXPLAIN QUERY PLAN で確認し、実データに近い状態で計測して決める、です。

次は複合インデックス

単一カラムのインデックスでもかなりの場面をカバーできますが、実際のクエリは複数カラムでの絞り込みや並び替えを同時にやることが多いものです。(a, b, c) のような複合インデックスはそこで効いてきますが、カラムの順序が思っている以上に重要です。次のページでこの話を扱います。

よくある質問

SQLite でインデックスを作るには？

基本は CREATE INDEX index_name ON table_name(column_name); です。一意制約を付けたいときは CREATE UNIQUE INDEX を使います。インデックス名はテーブル単位ではなくデータベース全体で一意でなければならない点に注意してください。削除は DROP INDEX index_name; でOKです。

どんなときにインデックスを張るべき？

WHERE でよく絞り込む列、JOIN のキー、ORDER BY に使う列が候補です。とくにテーブルが大きく、クエリで取得する行数が全体のごく一部、というケースで効果が出ます。一方で、何でもかんでも張ってしまうと INSERT / UPDATE / DELETE が遅くなり、ディスクも食います。張ったあとは必ず EXPLAIN QUERY PLAN でプランナが本当にそのインデックスを使っているか確認しましょう。

なぜ作ったインデックスが使われないの？

よくある原因はこのあたりです。テーブルが小さくフルスキャンの方が安いと判断された、列を関数で包んでいる（WHERE lower(email) = ... だと email のインデックスは効きません）、OR でインデックスのない列とつないでいる、統計情報が古い、など。ANALYZE で統計を更新し、EXPLAIN QUERY PLAN でプランナの選択を確認してみてください。

テーブルに張ってあるインデックスを一覧で見るには？

特定テーブルだけ見るなら PRAGMA index_list('table_name');。データベース全体なら sqlite_master を直接たたいて SELECT name, sql FROM sqlite_master WHERE type = 'index'; でOKです。sqlite_autoindex_* という名前のものは、PRIMARY KEY や UNIQUE 制約のために SQLite が自動で作ったインデックスです。

SQLite インデックス入門：CREATE INDEX と使いどころ