Strategi Index

Membuat index itu mudah — satu baris CREATE INDEX dan pencarian melompat dari scan penuh menjadi penyusuran B-tree. Persoalan sesungguhnya mulai muncul pada pertanyaan berikutnya: kolom mana yang sebaiknya masuk index, dalam urutan apa, dan apakah index itu sepadan dengan biayanya. Chapter Index dan Pencarian Cepat membangun fondasi berupa SCAN versus SEARCH; bab ini melangkah lebih jauh ke strategi: index komposit, covering index, index untuk ORDER BY, partial index, expression index, hingga ukuran biaya yang ditimbulkan setiap index pada setiap penulisan data.

Urutan Kolom pada Index Komposit #

Index komposit mengurutkan baris menurut kolom pertama, lalu kolom kedua, dan seterusnya. Konsekuensinya: query hanya bisa memakai index bila menyertakan kolom paling kiri. Kita gunakan skema berikut sepanjang bab ini:

sql
CREATE TABLE pelanggan (
  id INTEGER PRIMARY KEY,
  nama TEXT NOT NULL,
  kota TEXT NOT NULL,
  status TEXT NOT NULL
);

INSERT INTO pelanggan (nama, kota, status) VALUES
  ('Budi Santoso', 'Jakarta', 'aktif'),
  ('Siti Rahayu', 'Bandung', 'aktif'),
  ('Anton Wijaya', 'Surabaya', 'nonaktif'),
  ('Rina Marlina', 'Jakarta', 'aktif'),
  ('Dewi Lestari', 'Medan', 'nonaktif');

CREATE INDEX idx_pelanggan_kota_nama ON pelanggan(kota, nama);

Index (kota, nama) bekerja seperti direktori telepon yang dikelompokkan per kota, lalu per nama di dalam kota. Mencari “semua orang bernama Budi Santoso” tanpa menyebut kota memaksa pembacaan seluruh direktori:

sql
EXPLAIN QUERY PLAN SELECT * FROM pelanggan WHERE nama = 'Budi Santoso';

Output yang Dihasilkan:

text

QUERY PLAN
`--SCAN pelanggan

Begitu kolom kiri ikut disebut, index bisa dipakai — baik sendirian maupun bersama kolom keduanya:

sql
EXPLAIN QUERY PLAN SELECT * FROM pelanggan WHERE kota = 'Jakarta';

Output yang Dihasilkan:

text

QUERY PLAN
`--SEARCH pelanggan USING INDEX idx_pelanggan_kota_nama (kota=?)

sql
EXPLAIN QUERY PLAN SELECT * FROM pelanggan
WHERE kota = 'Jakarta' AND nama = 'Budi Santoso';

Output yang Dihasilkan:

text

QUERY PLAN
`--SEARCH pelanggan USING INDEX idx_pelanggan_kota_nama (kota=? AND nama=?)

Aturan ini dikenal sebagai leftmost prefix rule: index (a, b) melayani WHERE a = ... serta WHERE a = ... AND b = ..., tetapi tidak WHERE b = ... sendirian. Pilih urutan kolom berdasarkan query yang paling sering berjalan, bukan berdasarkan urutan kolom pada tabel.

Covering Index #

Setiap entri index menyimpan nilai kolom-kolomnya plus rowid tabel. Jika seluruh kolom yang dibutuhkan query — kolom filter maupun kolom hasil — sudah ada di dalam index, engine SQLite tidak perlu membuka tabel sama sekali. Bandingkan dua query berikut:

sql
EXPLAIN QUERY PLAN SELECT kota, nama FROM pelanggan WHERE kota = 'Jakarta';

Output yang Dihasilkan:

text

QUERY PLAN
`--SEARCH pelanggan USING COVERING INDEX idx_pelanggan_kota_nama (kota=?)

sql
EXPLAIN QUERY PLAN SELECT * FROM pelanggan WHERE kota = 'Jakarta';

Output yang Dihasilkan:

text

QUERY PLAN
`--SEARCH pelanggan USING INDEX idx_pelanggan_kota_nama (kota=?)

Query pertama memakai label COVERING INDEX karena kolom kota dan nama lengkap ada di index. Query kedua meminta SELECT * sehingga engine SQLite tetap harus membuka tabel untuk kolom status. Menambahkan kolom yang sering dibaca ke dalam index komposit adalah salah satu trik optimasi termurah; mengganti SELECT * dengan daftar kolom eksplisit adalah trik pasangannya.

Index untuk ORDER BY #

Index tidak hanya mempercepat filter — baris di dalamnya juga sudah terurut. Query berikut menyaring berdasarkan status lalu mengurutkan menurut nama, padahal index kita berawal dari kota, sehingga pengurutan masih dikerjakan manual:

sql
EXPLAIN QUERY PLAN SELECT * FROM pelanggan
WHERE status = 'aktif' ORDER BY nama;

Output yang Dihasilkan:

text
QUERY PLAN
|--SCAN pelanggan
`--USE TEMP B-TREE FOR ORDER BY

Index (status, nama) menuntaskan keduanya sekaligus — filter pada kolom pertama, urutan dari kolom kedua:

sql
CREATE INDEX idx_pelanggan_status_nama ON pelanggan(status, nama);
EXPLAIN QUERY PLAN SELECT * FROM pelanggan
WHERE status = 'aktif' ORDER BY nama;

Output yang Dihasilkan:

text

QUERY PLAN
`--SEARCH pelanggan USING INDEX idx_pelanggan_status_nama (status=?)

Baris USE TEMP B-TREE yang tadinya muncul kini hilang: hasil query keluar sudah terurut.

sql
SELECT nama, kota FROM pelanggan
WHERE status = 'aktif' ORDER BY nama;

Output yang Dihasilkan:

text
Budi Santoso|Jakarta
Rina Marlina|Jakarta
Siti Rahayu|Bandung

Partial Index #

Index tidak harus mencakup seluruh tabel. Sejak SQLite 3.8.0, klausa WHERE pada CREATE INDEX membatasi isi index hanya pada baris yang cocok:

sql
CREATE INDEX idx_pelanggan_aktif ON pelanggan(kota) WHERE status = 'aktif';

Index mini semacam ini lebih kecil dan lebih cepat diperbarui. Query planner memakainya hanya bila predikat query termasuk predikat index:

sql
EXPLAIN QUERY PLAN SELECT * FROM pelanggan
WHERE kota = 'Bandung' AND status = 'aktif';

Output yang Dihasilkan:

text

QUERY PLAN
`--SEARCH pelanggan USING INDEX idx_pelanggan_aktif (kota=?)

sql
EXPLAIN QUERY PLAN SELECT * FROM pelanggan WHERE kota = 'Bandung';

Output yang Dihasilkan:

text

QUERY PLAN
`--SEARCH pelanggan USING INDEX idx_pelanggan_kota_nama (kota=?)

Query kedua tidak memuat status = 'aktif', jadi partial index tidak bisa dipakai dan query planner kembali ke index penuh. Pola ini pas untuk data yang panas: pesanan belum dikirim, log belum diarsip, pelanggan masih aktif.

Expression Index #

Sejak SQLite 3.9.0, index bisa dibangun di atas hasil ekspresi, bukan sekadar kolom. Kasus klasiknya pencarian case-insensitive yang selama ini memaksa scan:

sql
CREATE INDEX idx_pelanggan_nama_lower ON pelanggan(lower(nama));
EXPLAIN QUERY PLAN SELECT * FROM pelanggan WHERE lower(nama) = 'budi santoso';

Output yang Dihasilkan:

text

QUERY PLAN
`--SEARCH pelanggan USING INDEX idx_pelanggan_nama_lower (<expr>=?)

Query harus menulis ulang ekspresi yang sama persis — lower(nama) — supaya query planner mengenalinya sebagai kolom index:

sql
SELECT * FROM pelanggan WHERE lower(nama) = 'budi santoso';

Output yang Dihasilkan:

text

1|Budi Santoso|Jakarta|aktif

Ekspresi lower() juga bisa menolong pencarian pola berawalan. Secara bawaan LIKE 'budi%' tidak bisa memakai index karena LIKE bersifat case-insensitive sementara index ber-collation BINARY — chapter Index dan Pencarian Cepat menyelesaikannya lewat COLLATE NOCASE. Alternatifnya, samakan sisi kolom ke huruf kecil lalu nyalakan pencocokan case-sensitive:

sql
PRAGMA case_sensitive_like = ON;
EXPLAIN QUERY PLAN SELECT * FROM pelanggan WHERE lower(nama) LIKE 'budi%';

Output yang Dihasilkan:

text

QUERY PLAN
`--SEARCH pelanggan USING INDEX idx_pelanggan_nama_lower (<expr>>? AND <expr><?)

Tanda <expr>>? AND <expr><? menandakan pencarian rentang pada ekspresi lower(nama) — pola LIKE diterjemahkan menjadi batas bawah dan batas atas. Nilai kolom sudah di-lowercase lewat index, jadi case-insensitivity yang hilang dari LIKE sudah ditangani manual.

Selektivitas dan Biaya Menulis #

Index paling berguna pada kolom dengan selektivitas tinggi: kolom kode yang bernilai unik membagi tabel jadi 100.000 kelompok berisi satu baris, sedangkan kolom status bernilai dua membagi tabel jadi dua kelompok besar. Index pada kolom bernilai sedikit tetap dipakai query planner, tetapi penghematannya kecil dibanding ruang dan biaya pembaruannya. Lawan selektivitas ini adalah biaya tulis: setiap INSERT, UPDATE, dan DELETE harus ikut memperbarui semua index tabelnya. Mari kita ukur dengan 200.000 baris produk — 100.000 sisipan pertama tanpa index tambahan, 100.000 berikutnya setelah tiga index dibuat:

sql
CREATE TABLE produk (
  id INTEGER PRIMARY KEY,
  kode TEXT NOT NULL,
  harga INTEGER NOT NULL,
  stok INTEGER NOT NULL
);

WITH RECURSIVE angka(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1 FROM angka WHERE x < 100000
)
INSERT INTO produk (kode, harga, stok)
SELECT 'BRG-' || printf('%05d', x), (x * 37) % 250000 + 1000, x % 100
FROM angka;

CREATE INDEX idx_produk_harga ON produk(harga);
CREATE INDEX idx_produk_stok ON produk(stok);
CREATE INDEX idx_produk_kode ON produk(kode);

WITH RECURSIVE angka(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1 FROM angka WHERE x < 100000
)
INSERT INTO produk (kode, harga, stok)
SELECT 'BRG-' || printf('%05d', 100000 + x), (x * 37) % 250000 + 1000, x % 100
FROM angka;

SELECT count(*) FROM produk;

Output yang Dihasilkan:

text

Di mesin penulis, 100.000 baris pertama tersimpan dalam sekitar 44 milidetik; 100.000 baris kedua memakan waktu sekitar 220 milidetik — hampir lima kali lebih lambat gara-gara tiga index yang ikut diperbarui di setiap sisipan. Ingat pula bahwa constraint UNIQUE dan PRIMARY KEY non-integer diam-diam membuat index sendiri, sebagaimana dibahas di chapter Index dan Pencarian Cepat.

Setelah index dirancang, satu pertanyaan masih tersisa: bagaimana query planner memilih di antara beberapa index yang tersedia? Chapter ANALYZE dan Statistik Query Planner menjawabnya.