Index dan Pencarian Cepat

Query WHERE kode = 'BRG-099999' terdengar cepat, tetapi tanpa index engine SQLite harus memeriksa tabel baris demi baris dari awal sampai akhir — full table scan. Pada 100.000 baris scan masih terasa ringan; pada puluhan juta baris setiap query menjadi kemacetan. Index memecahkan masalah ini dengan struktur B-tree, dan chapter ini membuktikan efeknya lewat EXPLAIN QUERY PLAN, lalu membedah kapan index justru tidak bisa dipakai.

Data Uji: 100.000 Baris #

Kita bangun dataset uji dengan WITH RECURSIVE — teknik dari chapter Common Table Expressions — sehingga tidak perlu memasukkan data satu per satu:

CREATE TABLE log_pencarian (
  id INTEGER PRIMARY KEY,
  kode TEXT NOT NULL,
  nilai INTEGER NOT NULL
);

WITH RECURSIVE angka(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1 FROM angka WHERE x < 100000
)
INSERT INTO log_pencarian (kode, nilai)
SELECT 'BRG-' || printf('%06d', x), (x * 7919) % 1000
FROM angka;

SELECT count(*) FROM log_pencarian;

Output yang Dihasilkan:

100000

Kolom kode berisi nilai unik BRG-000001 sampai BRG-100000, dan kolom nilai berisi angka yang tersebar — kombinasi yang realistis untuk menguji pencarian.

SCAN versus SEARCH #

EXPLAIN QUERY PLAN memperlihatkan strategi query planner tanpa benar-benar menjalankan query. Sebelum ada index:

EXPLAIN QUERY PLAN SELECT nilai FROM log_pencarian WHERE kode = 'BRG-099999';

Output yang Dihasilkan:

QUERY PLAN
`--SCAN log_pencarian

SCAN berarti seluruh tabel dipindai. Sekarang buat index pada kolom kode dan ulangi query yang sama:

CREATE INDEX idx_kode ON log_pencarian(kode);

EXPLAIN QUERY PLAN SELECT nilai FROM log_pencarian WHERE kode = 'BRG-099999';

Output yang Dihasilkan:

QUERY PLAN
`--SEARCH log_pencarian USING INDEX idx_kode (kode=?)

SEARCH ... USING INDEX berarti query planner menyusuri B-tree index untuk melompat langsung ke baris yang cocok — sekitar 17 langkah untuk 100.000 baris, karena jumlah langkah B-tree tumbuh secara logaritmik, bukan linier. Angka waktunya memang meyakinkan: di mesin penulis, satu pencarian turun dari sekitar 5 milidetik menjadi di bawah 1 milidetik, dan query yang mengulang pencarian 1.000 kali turun dari sekitar 6,2 detik menjadi 2 milidetik. Angka pastinya berbeda di mesin Anda, tetapi rasionya yang penting: ribuan kali lebih cepat.

Index Komposit dan Aturan Kolom Kiri #

Index bisa mencakup beberapa kolom sekaligus. idx_kode_nilai berikut mengurutkan baris berdasarkan kode, lalu nilai:

CREATE INDEX idx_kode_nilai ON log_pencarian(kode, nilai);

EXPLAIN QUERY PLAN SELECT nilai FROM log_pencarian WHERE kode = 'BRG-099999';

Output yang Dihasilkan:

QUERY PLAN
`--SEARCH log_pencarian USING COVERING INDEX idx_kode_nilai (kode=?)

Label COVERING INDEX muncul karena seluruh kebutuhan query — kolom carian kode dan kolom hasil nilai — sudah tersimpan di dalam index, sehingga engine SQLite tidak perlu membuka tabelnya sama sekali. Namun urutan kolom menentukan segalanya. Pencarian yang tidak menyertakan kolom paling kiri tidak bisa memakai index:

EXPLAIN QUERY PLAN SELECT * FROM log_pencarian WHERE nilai = 500;

Output yang Dihasilkan:

QUERY PLAN
`--SCAN log_pencarian

Index (kode, nilai) diurutkan menurut kode lebih dulu, sehingga baris bernilai nilai yang sama tersebar di seluruh index — persis seperti mencari semua orang dengan nama depan “Siti” di buku telepon yang diurutkan menurut nama belakang. Aturan praktisnya: index komposit (a, b) melayani WHERE a = ... dan WHERE a = ... AND b = ..., tetapi tidak WHERE b = ... sendirian.

Kapan Index Tidak Membantu #

Pola pencocokan yang tidak bisa diterjemahkan menjadi rentang nilai B-tree memaksa scan. Wildcard di awal pola adalah kasus pertama:

EXPLAIN QUERY PLAN SELECT * FROM log_pencarian WHERE kode LIKE '%099%';

Output yang Dihasilkan:

QUERY PLAN
`--SCAN log_pencarian

Fungsi yang dibalik pada kolom adalah kasus kedua — upper(kode) harus dihitung untuk setiap baris sebelum bisa dibandingkan:

EXPLAIN QUERY PLAN SELECT * FROM log_pencarian WHERE upper(kode) = 'BRG-099999';

Output yang Dihasilkan:

QUERY PLAN
`--SCAN log_pencarian

Jebakan yang lebih halus: LIKE 'BRG-099%' yang berawalan jelas tetap menghasilkan SCAN. Penyebabnya, LIKE bersifat case-insensitive sementara idx_kode diurutkan case-sensitive (collation BINARY), sehingga rentang B-tree tidak bisa mewakili hasilnya secara aman.

Index juga tidak gratis. Setiap INSERT, UPDATE, dan DELETE harus ikut memperbarui semua index tabelnya: di mesin penulis, memasukkan 100.000 baris melambat dari sekitar 35 milidetik menjadi 48 milidetik setelah satu index dibuat, dan setiap index memakan ruang penyimpanan tambahan. Karena itu index dipasang pada kolom yang benar-benar sering dicari, bukan pada semua kolom.

Index yang Dibuat Diam-diam #

Constraint UNIQUE dan PRIMARY KEY non-integer otomatis membutuhkan index untuk penegakannya, dan SQLite membuatnya tanpa diminta. Entri tersebut terlihat di sqlite_master dengan nama sqlite_autoindex_...:

CREATE TABLE contoh (
  id INTEGER PRIMARY KEY,
  kode TEXT NOT NULL UNIQUE,
  email TEXT
);
CREATE UNIQUE INDEX idx_email ON contoh(email);

SELECT type, name FROM sqlite_master WHERE tbl_name = 'contoh' ORDER BY name;

Output yang Dihasilkan:

table|contoh
index|idx_email
index|sqlite_autoindex_contoh_1

sqlite_autoindex_contoh_1 muncul dari constraint UNIQUE pada kode. Sebaliknya, INTEGER PRIMARY KEY tidak memunculkan index apa pun:

CREATE TABLE contoh_rowid (
  id INTEGER PRIMARY KEY,
  keterangan TEXT
);
SELECT type, name FROM sqlite_master WHERE tbl_name = 'contoh_rowid';

Output yang Dihasilkan:

table|contoh_rowid

Kolom INTEGER PRIMARY KEY adalah alias untuk rowid yang memang sudah tersusun sebagai B-tree — tabel itu sendiri sudah terindex tanpa biaya tambahan.

Menghapus dan Membuat Ulang Index #

Index yang tidak terpakai sebaiknya dihapus agar penulisan data kembali ringan:

DROP INDEX idx_kode;
CREATE INDEX idx_kode ON log_pencarian(kode);
CREATE INDEX IF NOT EXISTS idx_kode_nilai ON log_pencarian(kode, nilai);

Baris pertama menghapus idx_kode; baris kedua membuatnya kembali dengan definisi yang sama; baris ketiga tidak melakukan apa-apa karena idx_kode_nilai sudah ada — IF NOT EXISTS mencegah galat index idx_kode_nilai already exists saat skema dijalankan berulang.

Dengan index, tabel Anda siap menanggung data besar dan query yang menyaring jutaan baris. Materi optimasi yang lebih dalam — termasuk analisis EXPLAIN dan strategi query planner — dibahas pada section Performa dan Optimasi.