Composite Index di SQLite: Urutan Kolom dan Aturan Leftmost Prefix

Satu Index, Beberapa Kolom

Composite index, kadang disebut index multi kolom, adalah satu index yang dibangun di atas dua kolom atau lebih. Kamu membuatnya dengan menuliskan kolom-kolomnya secara berurutan:

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

CREATE INDEX idx_orders_customer_status
    ON orders(customer_id, status);

INSERT INTO orders(customer_id, status, created_at, total) VALUES
    (1, 'paid',    '2026-01-10', 49.00),
    (1, 'paid',    '2026-02-03', 12.50),
    (1, 'pending', '2026-02-14',  9.99),
    (2, 'paid',    '2026-01-22', 80.00),
    (2, 'refunded','2026-02-01', 25.00);

SELECT * FROM orders WHERE customer_id = 1 AND status = 'paid';

Index idx_orders_customer_status menyimpan entri dengan urutan berdasarkan customer_id lebih dulu, lalu berdasarkan status di dalam setiap customer. Urutan itulah intinya: semua hal lain tentang composite index berasal dari situ.

Gambaran Sederhananya: Buku Telepon yang Terurut

Bayangkan buku telepon zaman dulu. Entrinya diurutkan berdasarkan nama belakang, dan di dalam setiap nama belakang, berdasarkan nama depan. Seperti itulah tampilan index pada (last_name, first_name).

Ada pencarian yang murah, ada yang tidak:

• "Cari semua orang bernama Patel": mudah, semua Patel berada berdekatan.
• "Cari Priya Patel": mudah, lompat ke Patel, lalu telusuri sampai Priya.
• "Cari semua orang bernama Priya": lambat, kamu harus menelusuri setiap halaman. Priya tersebar di semua nama belakang.

Composite index di SQLite bekerja dengan cara yang sama. Kolom pertama adalah key urutan utama, dan kolom kedua hanya mengurutkan entri yang punya nilai kolom pertama yang sama.

Aturan Leftmost Prefix

SQLite hanya bisa memakai composite index untuk sebuah query kalau klausa WHERE membatasi leftmost prefix dari kolom-kolomnya, yaitu kolom-kolom paling kiri secara berurutan. Untuk index pada (a, b, c):

• Memfilter a: index dipakai.
• Memfilter a dan b: index dipakai.
• Memfilter a, b, dan c: index dipakai.
• Memfilter b saja, c saja, atau b dan c: index tidak dipakai.

Kamu bisa memastikannya langsung dengan EXPLAIN QUERY PLAN:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    kind TEXT NOT NULL,
    occurred_at TEXT NOT NULL
);

CREATE INDEX idx_events_user_kind_time
    ON events(user_id, kind, occurred_at);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id = 42 AND kind = 'login';

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE kind = 'login';

Rencana pertama melaporkan SEARCH events USING INDEX idx_events_user_kind_time. Rencana kedua kembali ke SCAN events: memfilter kind saja melewati kolom user_id di urutan pertama, jadi index-nya tidak berguna untuk query itu.

Urutan Kolom Adalah Keputusan Desain

Karena leftmost prefix itu penting, urutan kolom yang kamu tulis di CREATE INDEX adalah pilihan nyata, bukan soal gaya. Ada dua patokan praktis:

1. Taruh kolom yang paling sering kamu filter di urutan pertama. Kolom itu membuka pemakaian index untuk rentang query yang paling luas.
2. Taruh kolom kesamaan sebelum kolom rentang. SQLite bisa menelusuri index memakai =, lalu memindai rentang yang berurutan memakai <, >, atau BETWEEN, tapi hanya pada kolom terakhir yang dipakai.

CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    region TEXT NOT NULL,
    sold_at TEXT NOT NULL,
    amount REAL NOT NULL
);

-- Good: equality on region first, range on sold_at second.
CREATE INDEX idx_sales_region_time ON sales(region, sold_at);

INSERT INTO sales(region, sold_at, amount) VALUES
    ('EU', '2026-01-05', 120),
    ('EU', '2026-02-11', 80),
    ('US', '2026-01-09', 200),
    ('US', '2026-02-20', 150);

EXPLAIN QUERY PLAN
SELECT * FROM sales
WHERE region = 'EU' AND sold_at >= '2026-02-01';

Rencananya menampilkan SEARCH sales USING INDEX idx_sales_region_time (region=? AND sold_at>?). SQLite langsung melompat ke region = 'EU', lalu bergerak maju melewati rentang tanggal. Kalau urutan kolomnya dibalik menjadi (sold_at, region), query yang sama harus memindai setiap baris dalam rentang tanggal itu dan memeriksa ulang region di setiap baris.

Composite vs Beberapa Index Satu Kolom

Pertanyaan yang sering muncul: sebaiknya saya membuat satu index pada (a, b), atau dua index terpisah pada a dan b?

CREATE TABLE tickets (
    id INTEGER PRIMARY KEY,
    project_id INTEGER NOT NULL,
    assignee_id INTEGER NOT NULL,
    state TEXT NOT NULL
);

-- Option A: one composite index.
CREATE INDEX idx_tickets_proj_state ON tickets(project_id, state);

-- Option B (alternative): two single-column indexes.
-- CREATE INDEX idx_tickets_project ON tickets(project_id);
-- CREATE INDEX idx_tickets_state   ON tickets(state);

EXPLAIN QUERY PLAN
SELECT * FROM tickets
WHERE project_id = 7 AND state = 'open';

Untuk filter gabungan, composite index lebih cepat: SQLite langsung menuju entri (project_id, state) yang cocok. Dengan dua index satu kolom, SQLite biasanya memilih salah satu, memakainya untuk mempersempit baris, lalu memeriksa ulang kolom yang lain di setiap baris yang cocok. Kadang SQLite bisa memotongkan keduanya, tapi composite index adalah jawaban yang lebih rapi kalau kolom-kolom itu di-query bersamaan.

Kalau project_id dan state juga di-query secara mandiri, mungkin kamu butuh keduanya: composite index untuk filter gabungan, ditambah index satu kolom pada state untuk query yang hanya memfilter kolom itu.

Covering Index

Kalau sebuah index berisi setiap kolom yang dibutuhkan query, baik kolom filter maupun kolom yang dipilih, SQLite bisa menjawab query tanpa menyentuh tabel sama sekali. Itulah covering index, dan itu adalah kecepatan tertinggi yang bisa dicapai sebuah query.

CREATE TABLE invoices (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    issued_at TEXT NOT NULL,
    total REAL NOT NULL,
    notes TEXT
);

CREATE INDEX idx_invoices_cover
    ON invoices(customer_id, issued_at, total);

INSERT INTO invoices(customer_id, issued_at, total, notes) VALUES
    (1, '2026-01-12', 100, 'first'),
    (1, '2026-02-02', 250, NULL),
    (2, '2026-01-30',  75, 'rush');

EXPLAIN QUERY PLAN
SELECT issued_at, total FROM invoices WHERE customer_id = 1;

Rencananya menampilkan USING COVERING INDEX idx_invoices_cover. Query membaca issued_at dan total langsung dari index. notes dan id tidak dibutuhkan, jadi tabelnya sendiri tidak pernah dibuka. Menambahkan kolom ke composite index semata-mata untuk mencakup query yang sering dijalankan adalah pertukaran yang layak kalau query itu dijalankan terus-menerus.

Constraint UNIQUE Komposit

Composite index juga bisa menegakkan keunikan pada kombinasi kolom. Ini berguna kalau tidak ada satu kolom pun yang unik dengan sendirinya, tapi kombinasinya harus unik:

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL,
    course_id  INTEGER NOT NULL,
    enrolled_at TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_enrollments_pair
    ON enrollments(student_id, course_id);

INSERT INTO enrollments VALUES (1, 100, '2026-01-05');
INSERT INTO enrollments VALUES (1, 101, '2026-01-05');  -- ok, different course
INSERT INTO enrollments VALUES (1, 100, '2026-02-01');  -- fails: pair repeats

Insert ketiga memunculkan UNIQUE constraint failed: enrollments.student_id, enrollments.course_id. Pasangan yang sama sudah ada di index, jadi SQLite menolak duplikatnya.

Jebakan yang Perlu Diketahui

• OR di antara kolom yang bukan kolom pertama menghalangi index. WHERE a = 1 OR b = 2 pada index (a, b) biasanya sama sekali tidak bisa memakai index, karena SQLite perlu mempertimbangkan kedua cabang secara terpisah.
• Fungsi pada kolom yang di-index menonaktifkan index. WHERE lower(email) = 'x' tidak akan memakai index pada email. Buat index pada ekspresinya, atau normalisasi data saat insert.
• Index tidak gratis. Setiap index diperbarui pada setiap INSERT, UPDATE (pada kolom yang di-index), dan DELETE. Tiga composite index pada tabel yang sering ditulisi bisa mendominasi biaya penulisan.
• Jalankan ANALYZE setelah membuat index. Planner SQLite memakai statistik yang dikumpulkan ANALYZE untuk memilih di antara index yang tersedia. Tanpa statistik itu, ia kembali ke aturan perkiraan yang tidak selalu optimal.

Alur Kerja yang Praktis

Saat menyetel query yang lambat, putarannya biasanya seperti ini:

1. Jalankan EXPLAIN QUERY PLAN pada query untuk melihat apa yang dilakukan SQLite saat ini.
2. Kalau ia memindai, lihat klausa WHERE: kolom mana yang memakai kesamaan? Kolom mana yang memakai rentang? Apa yang dipilih?
3. Buat composite index dengan urutan kolom kesamaan dulu, kolom rentang kedua, lalu tambahkan kolom yang dipilih kalau covering membantu.
4. Jalankan ANALYZE.
5. Jalankan EXPLAIN QUERY PLAN lagi. Pastikan rencananya berubah dan index-nya dipakai.
6. Ukur waktu query sebelum dan sesudah pada data yang representatif.

Kalau kamu melewatkan langkah 6, tanggung sendiri risikonya. Index yang terlihat benar di rencana query tetap bisa lebih lambat dalam praktik kalau tabelnya kecil atau planner memilih jalur lain.

Berikutnya: Partial Index

Composite index mencakup setiap baris di tabel. Tapi sering kali, hanya sebagian kecil baris yang penting: tiket yang masih terbuka, job yang belum diproses, record yang belum dihapus. Partial index memungkinkanmu membuat index hanya untuk baris-baris itu, dengan klausa WHERE yang tertanam di index itu sendiri. Itu halaman berikutnya.