Index Parsial SQLite: CREATE INDEX ... WHERE

Index Parsial Hanya Mencakup Sebagian Baris

Index biasa punya satu entri untuk setiap baris di tabel. Index parsial hanya punya entri untuk baris yang cocok dengan klausa WHERE yang kamu berikan saat membuatnya. Index-nya lebih kecil, halaman yang perlu ditelusuri lebih sedikit, dan pekerjaannya lebih ringan untuk setiap insert dan update yang tidak menyentuh bagian ber-index itu.

Sintaksnya adalah CREATE INDEX biasa yang ditambahi 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 hanya berisi entri untuk baris dengan status = 'pending'. Pesanan yang sudah dikirim, dibatalkan, atau dikembalikan dananya sama sekali tidak ada di dalamnya. Jika 95% isi tabel orders-mu adalah data lama dan kamu kebanyakan meng-query pesanan yang masih terbuka, index-nya 20 kali lebih kecil dengan kecepatan query yang sama.

Kapan Planner Benar-benar Memakainya

Index parsial hanya bisa dipakai jika SQLite bisa membuktikan bahwa query-mu terbatas pada baris yang sama dengan yang dicakup index. Cara paling bersih adalah mengulang klausa WHERE milik index di dalam query:

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;

Rencana query-nya seharusnya menyebut USING INDEX idx_orders_pending. Hapus status = 'pending' dari query, dan planner kembali ke scan seluruh tabel, karena planner tidak punya cara untuk tahu bahwa query itu tetap berada di dalam subset yang diberi index.

Patokannya: WHERE milik query harus menyiratkan WHERE milik index. Persamaan pada kolom dan nilai yang sama adalah kasus yang aman dan jelas. Pertidaksamaan dan OR lebih rumit; cek dengan EXPLAIN QUERY PLAN.

Kenapa Repot-repot: Tiga Keuntungannya

Tiga alasan konkret kenapa index parsial sepadan:

1. Lebih kecil di disk. Hanya baris yang cocok yang disimpan. Untuk beban kerja "hanya 1% tabel yang sering diakses", ukuran index-nya kira-kira 1% dari index penuh.
2. Penulisan lebih murah. Insert dan update hanya menyentuh index jika barisnya cocok dengan filter. Insert dengan status = 'shipped' pada tabel di atas sama sekali tidak menyentuh idx_orders_pending.
3. Kecepatan pencarian yang sama. Pencarian B-tree bersifat logaritmik terhadap ukuran index. Index yang lebih kecil membuat pencarian sedikit lebih cepat, tapi keuntungan yang lebih besar ada di sekitarnya: cache miss lebih sedikit, I/O lebih sedikit.

Jika sebaran nilai sebuah kolom sangat timpang, yaitu kebanyakan baris punya satu nilai dan kamu hanya peduli pada nilai-nilai lain yang langka, itulah kasus klasik untuk index parsial.

Unique Index Parsial (Fitur Andalannya)

Constraint UNIQUE biasa berlaku untuk setiap baris. Ini jadi masalah begitu kamu memakai soft delete:

-- Fails: there are two rows with email = 'a@x.com', even though one is deleted.
CREATE UNIQUE INDEX idx_users_email ON users(email);

Unique index parsial membuatmu bisa memaksakan keunikan hanya pada baris yang penting:

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'),  -- soft-deleted
    ('a@x.com', '2026-01-05'),  -- soft-deleted again
    ('a@x.com', NULL);          -- one live row, allowed

SELECT email, deleted_at FROM users;

Tiga baris, email yang sama, tidak ada pelanggaran constraint, karena hanya baris dengan deleted_at IS NULL yang ikut dalam pemeriksaan keunikan. Coba masukkan baris aktif kedua dengan email yang sama, dan SQLite akan memunculkan UNIQUE constraint failed.

Pola ini muncul di mana-mana: satu langganan aktif per pelanggan, satu alamat utama per pengguna, satu invoice terbuka per pesanan. Unique index parsial menyatakannya secara langsung.

Memberi Index di Sekitar NULL

NULL berinteraksi secara aneh dengan index. Tujuan yang umum adalah "abaikan NULL sepenuhnya". Misalnya, kamu punya kolom external_id yang jarang terisi, di mana kebanyakan barisnya NULL, tapi yang terisi harus unik:

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;

Dua NULL bisa hidup berdampingan dengan tenang; baris EXT-001 dan EXT-002 dijamin unik. Index-nya juga lebih kecil, karena baris NULL sama sekali tidak disimpan, sehingga pencarian berdasarkan external_id tetap cepat meskipun tabelnya terus membesar.

Apa yang Boleh Dirujuk oleh Filter

Klausa WHERE pada index parsial cukup ketat. Klausa itu boleh merujuk:

• Kolom-kolom dari tabel yang diberi index.
• Konstanta literal.
• Sekumpulan kecil fungsi bawaan yang deterministik.

Klausa itu tidak boleh merujuk:

• Tabel lain.
• Subquery.
• Fungsi non-deterministik seperti random() atau CURRENT_TIMESTAMP.
• Parameter atau variabel.

Ini masuk akal, karena SQLite harus menghitung filternya di setiap insert dan update baris, dan hasilnya harus stabil. Jadi yang seperti ini bisa:

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');

Tapi WHERE created_at > date('now') tidak bisa, karena date('now') berubah seiring waktu, sehingga kumpulan baris yang diberi index akan bergeser tanpa SQLite sadari.

Alur Pemeriksaan Sederhana

Saat kamu menambahkan index parsial, lakukan tiga pemeriksaan:

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. Does the planner pick the index?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'queued' ORDER BY run_at LIMIT 10;

-- 2. Does it ignore queries outside the filter?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'done' ORDER BY run_at;

-- 3. Does it skip queries missing the filter entirely?
EXPLAIN QUERY PLAN
SELECT id FROM jobs ORDER BY run_at;

Query 1 seharusnya memakai idx_jobs_runnable. Query 2 dan 3 seharusnya kembali ke scan (atau ke index lain, jika kamu punya). Jika planner memilih index parsial untuk query yang tidak kamu duga, baca ulang filternya, karena mungkin cakupannya lebih luas dari yang kamu kira.

Kapan Tidak Perlu Memakainya

Index parsial itu alat yang tajam. Alasan untuk tidak memakainya:

• Filternya cocok dengan sebagian besar tabel. Jika "aktif" mencakup 90% barismu, index parsial hanyalah index biasa dengan langkah tambahan. Cukup beri index pada kolomnya.
• Query-mu tidak menyertakan filternya secara harfiah. Jika kodemu memakai ORM yang membentuk WHERE status IN (?, ?, ?) atau menghitung filternya secara dinamis, planner sering kali tidak mengenali kecocokannya. Uji dengan EXPLAIN QUERY PLAN, jangan berasumsi.
• Subset yang sering diakses bergeser seiring waktu. Index parsial untuk "pesanan 30 hari terakhir" terdengar menarik, tapi tidak bisa dituliskan, karena filternya harus deterministik. Kamu harus membangun ulang index-nya, atau memilih skema yang berbeda (tabel recent_orders terpisah atau boolean archived yang kamu ubah setiap malam).

Jika filternya stabil dan cocok dengan sebagian kecil dari tabel yang besar, index parsial termasuk penyetelan paling berdampak yang bisa kamu lakukan di SQLite.

Selanjutnya: Membaca Rencana Query

Sebagian besar halaman ini mengandalkan EXPLAIN QUERY PLAN untuk memastikan sebuah index benar-benar dipakai. Alat itu layak mendapat halamannya sendiri: cara membaca output-nya, arti kata kuncinya, dan cara membedakan pencarian index yang lancar dari full scan yang diam-diam terjadi. Itulah topik berikutnya.