Indeks: kapan dan di kolom mana

Tanpa indeks, mencari satu baris berarti membaca seluruh tabel satu per satu. Dokumentasi SQLite menyebutnya pemindaian penuh (full table scan): wajar untuk tujuh baris, tetapi pada tujuh juta baris bisa membaca berkas berukuran megabita demi satu angka. Tabel SQLite tersimpan urut menurut rowid, sehingga pencarian menurut rowid memakai pencarian biner yang waktunya sebanding log N, bukan N. Indeks memberi keuntungan yang sama untuk kolom lain: salinan kolom itu yang terurut, bersama rowid barisnya.

PostgreSQL membandingkan indeks dengan indeks di belakang buku: istilah yang sering dicari dikumpulkan secara alfabetis supaya pembaca tidak perlu membaca seluruh buku. Biayanya juga dicatat di dokumentasi yang sama: setelah dibuat, indeks harus terus diselaraskan dengan tabel, sehingga menambah beban pada setiap INSERT, UPDATE, dan DELETE; indeks yang jarang atau tidak pernah dipakai kueri karena itu dihapus.

CREATE INDEX idx_peminjaman_buku ON peminjaman(buku_id);
CREATE UNIQUE INDEX idx_buku_isbn ON buku(isbn);  -- menolak ISBN ganda

Kandidat pertama untuk indeks adalah kolom kunci asing di tabel anak. Dokumentasi kunci asing SQLite menjelaskan alasannya: setiap kali baris induk dihapus, SQLite menjalankan pencarian SELECT rowid FROM tabel_anak WHERE kunci_anak = ? untuk menemukan anaknya. Indeks tidak wajib di kolom itu, tetapi hampir selalu bermanfaat. Kunci primer induk sudah punya indeks, karena PostgreSQL dan SQLite mewajibkan kolom yang dirujuk bersifat unik. Indeks UNIQUE sekaligus menjadi batasan: SQLite menolak nilai ganda, tetapi menganggap setiap NULL berbeda satu sama lain.