KAL
Menu

CREATE INDEX

Index satu kolom, komposit, dan covering index dengan urutan kolom yang benar.

Daftar isi

Topik ini membahas pernyataan CREATE INDEX dalam bentuk-bentuk yang lazim dipakai, yaitu index satu kolom, index komposit, kolom berarah DESC, covering index, dan UNIQUE INDEX. Setiap bentuk disertai rencana eksekusi yang membuktikan efeknya. Audit index yang sudah terpasang melalui PRAGMA index_list dan PRAGMA index_info menutup pembahasannya.

Index satu kolom#

CREATE INDEX membuat index pada tabel yang sudah ada dengan sintaks CREATE INDEX nama_index ON nama_tabel (kolom). Nama index merupakan objek tersendiri di basis data dan harus unik di seluruh basis data, sehingga kebiasaan menamainya idx_nama_tabel_nama_kolom memudahkan pembacaan skema. Kata IF NOT EXISTS mencegah error ketika skrip inisialisasi dijalankan ulang terhadap index yang sudah ada.

sql 
CREATE TABLE buku (
  id      INTEGER PRIMARY KEY,
  judul   TEXT NOT NULL,
  penulis TEXT NOT NULL,
  tahun   INTEGER NOT NULL
);
INSERT INTO buku (judul, penulis, tahun) VALUES
  ('Kisah Kota Tua', 'Sari Wulandari', 2018),
  ('Anak Sungai', 'Rahmat Hidayat', 2021),
  ('Musim di Ujung Jalan', 'Lina Marlina', 2019),
  ('Rumah Pohon', 'Lina Marlina', 2022),
  ('Jalan Berliku', 'Sari Wulandari', 2016),
  ('Danau Sepi', 'Lina Marlina', 2017),
  ('Kota Bertembok', 'Rahmat Hidayat', 2020),
  ('Lentera Malam', 'Dewi Anggraini', 2023);
CREATE INDEX idx_buku_penulis ON buku(penulis);
SELECT judul, tahun FROM buku WHERE penulis = 'Lina Marlina' ORDER BY tahun;
text Keluaran 
Danau Sepi|2017
Musim di Ujung Jalan|2019
Rumah Pohon|2022

Tiga baris milik Lina Marlina dikembalikan terurut menurut tahun. Pada tabel sekecil ini planner bahkan boleh memilih pemindaian, karena seluruh tabel muat dalam beberapa halaman dan pemindaian pun murah, sehingga keberadaan index belum tentu terlihat dari keluaran. Efek index baru teramati dengan jelas pada tabel besar, dan bukti pemakaiannya diperiksa dengan EXPLAIN QUERY PLAN pada bagian berikutnya.

Index komposit dan urutan kolom#

Index komposit mengindeks beberapa kolom sekaligus dengan satu pernyataan, misalnya CREATE INDEX idx_buku_penulis_tahun ON buku (penulis, tahun). Isian index tersebut terurut pertama menurut penulis, lalu menurut tahun untuk nilai penulis yang sama, sebagaimana pengurutan dua tingkat pada ORDER BY penulis, tahun. Satu index komposit dengan demikian melayani beberapa bentuk syarat tanpa index tambahan.

Aturan urutan kolomnya tegas, yaitu kolom yang dicari dengan kesamaan ditempatkan lebih dahulu dan kolom rentang ditempatkan kemudian. Kolom kedua hanya dapat dilayani bila kolom pertama dibatasi dengan kesamaan, karena hanya pada kelompok nilai pertama yang sama isian kolom kedua berurutan. Kueri yang menyaring tahun saja tidak dapat melompat pada index ini, sebab nilai tahun tersebar di seluruh index. Blok berikut membuktikan kedua keadaan itu dengan EXPLAIN QUERY PLAN pada tabel 20000 baris.

sql 
CREATE TABLE buku (
  id      INTEGER PRIMARY KEY,
  judul   TEXT NOT NULL,
  penulis TEXT NOT NULL,
  tahun   INTEGER NOT NULL
);
INSERT INTO buku (judul, penulis, tahun)
WITH RECURSIVE deret(i) AS (
  SELECT 1
  UNION ALL
  SELECT i + 1 FROM deret WHERE i < 20000
)
SELECT 'Buku ' || i, 'Penulis ' || (1 + (i % 200)), 1950 + (i % 75) FROM deret;
CREATE INDEX idx_buku_penulis_tahun ON buku(penulis, tahun);
EXPLAIN QUERY PLAN SELECT judul FROM buku WHERE penulis = 'Penulis 7' AND tahun >= 2020;
EXPLAIN QUERY PLAN SELECT judul FROM buku WHERE tahun >= 2020;
text Keluaran 
QUERY PLAN
`--SEARCH buku USING INDEX idx_buku_penulis_tahun (penulis=? AND tahun>?)
QUERY PLAN
`--SCAN buku

Rencana pertama menunjukkan SEARCH dengan tanda (penulis=? AND tahun>?), yang berarti kedua kolom dilayani index sekaligus. Rencana kedua menunjukkan SCAN untuk kueri yang hanya menyaring tahun, karena tahun bukan awalan index. Index komposit (penulis, tahun) tetap melayani kueri yang menyaring penulis saja, sehingga aturan awalan ini sekaligus menentukan kueri mana yang gagal dilayani.

Kolom dengan arah DESC#

Setiap kolom index dapat diberi arah urut ASC atau DESC, misalnya CREATE INDEX idx_buku_tahun_desc ON buku (tahun DESC, judul). Nilai tahun disimpan menurun pada index tersebut, sedangkan judul mengikuti menaik. SQLite dapat membaca index dari dua arah, sehingga ORDER BY satu kolom biasanya sudah dilayani index ASC, tetapi pengurutan beberapa kolom dengan arah berlawanan memerlukan index yang arah kolomnya cocok.

sql 
CREATE TABLE buku (
  id      INTEGER PRIMARY KEY,
  judul   TEXT NOT NULL,
  penulis TEXT NOT NULL,
  tahun   INTEGER NOT NULL
);
INSERT INTO buku (judul, penulis, tahun)
WITH RECURSIVE deret(i) AS (
  SELECT 1
  UNION ALL
  SELECT i + 1 FROM deret WHERE i < 20000
)
SELECT 'Buku ' || i, 'Penulis ' || (1 + (i % 200)), 1950 + (i % 75) FROM deret;
EXPLAIN QUERY PLAN SELECT judul, tahun FROM buku ORDER BY tahun DESC, judul;
CREATE INDEX idx_buku_tahun_desc ON buku(tahun DESC, judul);
EXPLAIN QUERY PLAN SELECT judul, tahun FROM buku ORDER BY tahun DESC, judul;
text Keluaran 
QUERY PLAN
|--SCAN buku
`--USE TEMP B-TREE FOR ORDER BY
QUERY PLAN
`--SCAN buku USING COVERING INDEX idx_buku_tahun_desc

Sebelum index dibuat, rencananya memuat baris USE TEMP B-TREE FOR ORDER BY, yaitu tanda bahwa hasil harus diurutkan dahulu oleh penyangga sementara. Setelah index (tahun DESC, judul) dibuat, baris tersebut hilang karena pembacaan index sudah sesuai urutan yang diminta, dan kuerinya kini terlayani sebagai covering index juga. Menghilangkan pengurutan tambahan pada kueri yang sering dijalankan merupakan salah satu guna utama kolom DESC.

Covering index#

Covering index adalah index yang memuat seluruh kolom yang dibutuhkan kueri, sehingga tabel tidak perlu dikunjungi sama sekali. SQLite tidak memiliki klausa INCLUDE seperti basis data lain, dan cara membentuknya adalah menambahkan kolom tambahan sebagai kolom index terakhir. Pada index (penulis, judul), kueri yang meminta judul untuk penulis tertentu terlayani sepenuhnya oleh index tanpa satu pun kunjungan tabel.

sql 
CREATE TABLE buku (
  id      INTEGER PRIMARY KEY,
  judul   TEXT NOT NULL,
  penulis TEXT NOT NULL,
  tahun   INTEGER NOT NULL
);
INSERT INTO buku (judul, penulis, tahun)
WITH RECURSIVE deret(i) AS (
  SELECT 1
  UNION ALL
  SELECT i + 1 FROM deret WHERE i < 20000
)
SELECT 'Buku ' || i, 'Penulis ' || (1 + (i % 200)), 1950 + (i % 75) FROM deret;
CREATE INDEX idx_buku_penulis_judul ON buku(penulis, judul);
EXPLAIN QUERY PLAN SELECT judul FROM buku WHERE penulis = 'Penulis 7';
EXPLAIN QUERY PLAN SELECT tahun FROM buku WHERE penulis = 'Penulis 7';
text Keluaran 
QUERY PLAN
`--SEARCH buku USING COVERING INDEX idx_buku_penulis_judul (penulis=?)
QUERY PLAN
`--SEARCH buku USING INDEX idx_buku_penulis_judul (penulis=?)

Rencana pertama bertuliskan USING COVERING INDEX karena judul yang diminta sudah berada di dalam index. Rencana kedua bertuliskan USING INDEX tanpa kata COVERING, karena kolom tahun tidak termuat, sehingga setiap baris yang cocok masih harus diambil dari tabel melalui rowidnya. Menambahkan satu kolom ke dalam index adalah penukaran sederhana antara ruang tambahan dan satu kunjungan tabel yang terhindar untuk setiap baris.

UNIQUE INDEX dan audit dengan PRAGMA#

UNIQUE INDEX menjalankan dua peran sekaligus, yaitu index untuk mempercepat pencarian dan batasan yang menolak nilai duplikat. Penyisipan judul yang sudah ada ditolak dengan pesan error UNIQUE constraint failed, dan pemeriksaan duplikatnya dijalankan melalui pencarian pada index yang sama. Index otomatis juga tercipta dari kata UNIQUE pada CREATE TABLE dan dari PRIMARY KEY, dengan asal yang berbeda pada keluaran PRAGMA.

sql 
CREATE TABLE buku (
  id      INTEGER PRIMARY KEY,
  judul   TEXT NOT NULL,
  penulis TEXT NOT NULL,
  tahun   INTEGER NOT NULL
);
INSERT INTO buku (judul, penulis, tahun) VALUES
  ('Kisah Kota Tua', 'Sari Wulandari', 2018),
  ('Anak Sungai', 'Rahmat Hidayat', 2021),
  ('Musim di Ujung Jalan', 'Lina Marlina', 2019);
CREATE INDEX idx_buku_penulis ON buku(penulis);
CREATE UNIQUE INDEX idx_buku_judul_unik ON buku(judul);
PRAGMA index_list(buku);
PRAGMA index_info(idx_buku_penulis);
text Keluaran 
0|idx_buku_judul_unik|1|c|0
1|idx_buku_penulis|0|c|0
0|2|penulis

Keluaran PRAGMA index_list(buku) memuat lima kolom, yaitu seq, name, unique, origin, dan partial. Baris idx_buku_judul_unik bertanda unique 1, sedangkan idx_buku_penulis bertanda 0, dan keduanya berasal dari CREATE INDEX yang ditandai huruf c. PRAGMA index_info(idx_buku_penulis) merinci kolom indexnya, yaitu urutan 0 dengan cid 2 yang merujuk kolom penulis pada definisi tabel. Dua pragma ini menjadi alat audit utama ketika jumlah index mulai banyak, sebagaimana dipraktikkan pada pembahasan anti-pola index.

Ringkasan#

  • CREATE INDEX satu kolom melayani kesamaan dan rentang pada kolom itu, sedangkan index komposit menambahkan kolom berikutnya yang hanya dilayani bila kolom sebelumnya dibatasi kesamaan.
  • Aturan urutan kolom komposit adalah kesamaan dahulu dan rentang kemudian, karena urutan itulah yang menentukan kueri yang dilayani.
  • Kolom DESC menghilangkan pengurutan tambahan untuk ORDER BY campuran, dan covering index menghilangkan kunjungan tabel dengan menambahkan kolom yang dibutuhkan sebagai kolom index terakhir.
  • UNIQUE INDEX sekaligus menjadi batasan anti duplikat, dan PRAGMA index_list beserta index_info memeriksa index yang terpasang.

Referensi#