Index SQLite: CREATE INDEX, Kapan dan Kenapa Dipakai

Apa Sebenarnya Index Itu

Index adalah struktur data terpisah, yaitu sebuah B-tree yang terurut, yang membuat SQLite bisa menemukan baris berdasarkan nilai sebuah kolom tanpa memindai seluruh tabel. Tanpa index, query seperti WHERE email = 'rosa@example.com' membaca setiap baris dan memeriksanya satu per satu. Dengan index pada email, SQLite menelusuri pohonnya dalam kira-kira log(n) langkah dan langsung melompat ke baris yang cocok.

Percepatan itu tidak gratis. Index adalah salinan kolom yang diberi index ditambah penunjuk kembali ke barisnya. Setiap INSERT, UPDATE pada kolom ber-index, dan DELETE juga harus memperbarui index. Pemakaian disk naik. Throughput penulisan sedikit turun. Kesepakatannya begini: kamu membayar saat menulis, dan menghemat jauh lebih banyak saat membaca.

Membuat Index

Sintaks dasarnya:

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

Konvensi penamaan: kebanyakan tim memakai idx_<table>_<column> supaya jelas index itu untuk apa. Namanya harus unik di seluruh database, bukan hanya di tabel itu. Itulah sebabnya nama tabel ikut menjadi bagian dari nama index.

Untuk menghapusnya:

DROP INDEX idx_users_email;

Index murni penopang performa. Menghapus index tidak pernah memengaruhi datamu, hanya seberapa cepat query berjalan.

Unique Index

Unique index punya dua tugas: mempercepat pencarian dan memastikan tidak ada dua baris yang punya nilai sama pada kolom ber-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');

Insert ketiga gagal dengan UNIQUE constraint failed: accounts.username. SQLite sudah otomatis membuat unique index untuk kolom PRIMARY KEY dan UNIQUE, dan namanya akan terlihat seperti sqlite_autoindex_<table>_<n>. Kamu hanya perlu menulis CREATE UNIQUE INDEX jika constraint itu tidak dideklarasikan di tabelnya sendiri.

Apa yang Sebenarnya Dilakukan Planner

Menambahkan index tidak menjamin SQLite akan memakainya. Query planner memilih strategi untuk setiap query, dan kamu bisa melihat pilihannya dengan 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;

Cari SEARCH ... USING INDEX idx_orders_customer di output. Itu artinya index sedang dipakai. Jika kamu melihat SCAN orders, planner memutuskan bahwa scan seluruh tabel lebih murah (sering kali benar untuk tabel yang sangat kecil), atau bentuk query-mu membuat index tidak bisa dipakai. Nanti ada satu dokumen khusus tentang cara membaca rencana query ini.

Kapan Index Tidak Akan Dipakai

Index punya beberapa titik buta yang sudah terkenal. Masing-masing contoh ini membuat index pada email tidak berguna:

-- Function wraps the column
SELECT * FROM users WHERE lower(email) = 'rosa@example.com';

-- Leading wildcard in LIKE
SELECT * FROM users WHERE email LIKE '%@example.com';

-- Type mismatch forces a conversion
SELECT * FROM users WHERE email = 12345;

B-tree diurutkan berdasarkan nilai email apa adanya, jadi apa pun yang mengubah kolom itu saat query berjalan memaksa terjadinya scan. Solusinya beragam: simpan data yang sudah dinormalisasi (kolom email_lower), pakai expression index (CREATE INDEX idx ON users(lower(email))), atau pakai full-text search milik SQLite untuk pencocokan substring.

Covering Index

Jika sebuah index berisi semua kolom yang dibutuhkan query, SQLite bisa menjawab query itu tanpa menyentuh tabelnya sama sekali. Itulah yang disebut covering index. Caranya adalah menyertakan kolom tambahan di definisi index:

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;

Karena kedua kolom yang diminta query ada di dalam index, SQLite melaporkan USING COVERING INDEX. Tidak perlu mengambil baris. Covering index adalah salah satu optimasi paling berdampak untuk jalur baca yang sering dipakai, dengan konsekuensi ukuran index yang lebih besar. Index multi-kolom punya topiknya sendiri, dan dokumen berikutnya membahasnya dengan tuntas.

Melihat dan Memeriksa Index

Ada dua cara untuk melihat index yang ada:

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

Query itu memberimu setiap index di database beserta pernyataan CREATE-nya. Untuk satu tabel saja, PRAGMA index_list('products'); menampilkan index milik tabel itu, dan PRAGMA index_info('idx_products_name'); menampilkan kolom apa saja yang di-index oleh masing-masing index. Apa pun yang diawali sqlite_autoindex_ dibuat otomatis untuk constraint PRIMARY KEY atau UNIQUE, dan index seperti itu tidak bisa kamu hapus.

Kapan Tidak Perlu Menambahkan Index

Beberapa situasi saat menambahkan index justru memperburuk keadaan:

• Tabel yang sangat kecil. Beberapa ratus baris bisa dipindai dalam hitungan mikrodetik. Planner kemungkinan besar akan mengabaikan index-nya, dan kamu menambah beban penulisan tanpa hasil apa pun.
• Kolom yang sering ditulis tapi jarang di-query. Setiap penulisan memperbarui setiap index. Memberi index pada kolom yang hampir tidak pernah kamu filter hanya menambah biaya.
• Kolom berkardinalitas rendah jika berdiri sendiri. Index pada kolom status yang hanya punya tiga kemungkinan nilai tidak banyak mempersempit hasil. Index itu masih bisa membantu sebagai kolom kedua dari index komposit, atau sebagai index parsial, tapi jika berdiri sendiri sering kali tidak sepadan.
• Sudah tercakup. Jika kamu punya index pada (a, b), kamu tidak perlu index lagi pada (a). SQLite memakai kolom terdepan dari index komposit untuk query yang hanya memfilter a.

Jawaban jujur untuk pertanyaan "perlukah saya menambahkan index ini?" hampir selalu: coba, jalankan EXPLAIN QUERY PLAN, ukur dengan data yang realistis, lalu putuskan.

Selanjutnya: Index Komposit

Index satu kolom sudah mencakup banyak kebutuhan, tapi query sungguhan sering memfilter dan mengurutkan beberapa kolom sekaligus. Index komposit, yaitu index pada (a, b, c), menangani hal itu, dan urutan kolomnya lebih penting daripada yang orang kira. Itu topik halaman berikutnya.