Tips Memilih Jenis Indeks SQLite

Aditya Phra•

Salah satu optimasi yang wajib dilakukan apabila datanya banyak sekali adalah membuat indeks untuk mempercepat kueri SQL.

Apalagi kalau kamu memakai hosting yang serverless seperti Cloudflare D1, yang penggunaannya dihitung dari banyaknya baris yang dibaca dari setiap kueri yang tereksekusi. Jadinya optimasi kueri SQL di awal perlu agar tagihan tidak boncos kedepannya.

Ada lima jenis indeks yang akan kubahas di dalam tulisan ini, yaitu indeks pada satu kolom, indeks pada banyak kolom, indeks pada kolom dengan data unik (tidak boleh ada data ganda), indeks sebagian/parsial, dan indeks pada ekspresi.

Indeks satu kolom

Indeks satu kolom dipakai untuk mempercepat pencarian data berdasarkan data satu kolom saja.

Cara membuat indeks satu kolom:

CREATE INDEX user_username_idx
ON user(username);

Penjelasannya:

  • user_username_idx adalah nama indeks, sebisa mungkin nama indeks agar mudah dipahami seperti contoh di atas yang memakai format [nama_tabel]_[nama_kolom]_idx
  • user adalah nama tabel yang akan diindeks
  • username adalah nama kolom yang akan diindeks

Cara kuerinya:

SELECT * FROM user WHERE username = 'joko';

Seharusnya kalau indeks berhasil dibuat, kueri jadi lebih ngebut, cara memastikan apakah indeks beneran dipakai, bisa memakai EXPLAIN QUERY PLAN.

Kalau tidak memakai indeks itu seperti ini:

sqlite> EXPLAIN QUERY PLAN
   ...> SELECT * FROM user WHERE username = 'joko';
QUERY PLAN
`--SCAN user

Sedangkan kalau memakai indeks seperti ini:

sqlite> EXPLAIN QUERY PLAN
   ...> SELECT * FROM user WHERE username = 'joko';
QUERY PLAN
`--SEARCH user USING COVERING INDEX user_username_idx (username=?)

Perbedaannya sangat kerasa kalau datanya sangat banyak, kueri SQL yang tidak memakai indeks sangat lama daripada yang memakai indeks.

Untuk menghapus indeks:

DROP INDEX user_username_idx;

Indeks unik

Jenis indeks ini untuk memastikan agar setiap data yang masuk ke kolom tidak boleh ada data ganda.

Contoh jenis data yang biasa memakai indeks unik antara lain email, username, dan nama path (URL).

Cara membuat indeks unik:

CREATE UNIQUE INDEX user_email_idx
ON user(email);

Cara kuerinya:

SELECT * FROM user WHERE email = 'hello@example.com';

Indeks pada ekspresi

Jenis indeks dibuat berdasarkan hasil dari sebuah ekspresi yang data kolomnya dievaluasi terlebih dahulu sebelum dimasukkan ke indeks.

Misalnya dengan jenis indeks ini, kita bisa mencari data pada kolom username tanpa memandang besar kecil huruf dan tanpa perlu membuat kolom baru atau memakai klausa LIKE.

Cara membuat indeks berdasarkan ekspresi fungsi lower(username):

CREATE INDEX user_username_lower_idx
ON user(lower(username));

Cara kuerinya:

SELECT * FROM user
WHERE lower(username) = 'joko';

Contoh lain, yang indeks ini berdasarkan perhitungan kolom upvotes dan downvotes, tujuannya agar pos yang mempunyai skor tertinggi berada di atas.

CREATE INDEX post_score_idx
ON post(upvotes - downvotes);

Cara kuerinya:

SELECT * FROM post
ORDER BY upvotes - downvotes DESC;

Indeks parsial/sebagian

Jenis indeks yang ini bisa menghemat ukuran indeks dengan mengindeks data kolom yang perlu saja, tidak seperti indeks biasanya yang mengindeks seluruh data kolom.

Misalnya, kueri yang dipakai hanya untuk mencari pengguna yang tidak aktif (active = 0), ukuran indeks bisa dihemat dengan tidak memasukkan data kolom yang berisi pengguna aktif (active = 1) dengan indeks parsial.

Cara membuat indeks parsial:

CREATE INDEX user_active_idx
ON user(active);

Cara kuerinya:

SELECT * FROM user
WHERE active = 0;

Indeks dua kolom atau lebih

Nama lain untuk jenis indeks ini adalah indeks multikolom atau indeks komposit.

Cara membuat indeks multikolom:

CREATE INDEX index_name_idx
ON table_name(col_1, col_2, col_3);

Cara kuerinya:

SELECT * FROM table_name
WHERE col_1 = 'val_1' AND col_2 = 'val_2' AND col_3 = 'val_3';

Hal yang perlu diperhatikan kalau memakai jenis indeks ini, urutan kolom yang dibandingkan di dalam WHERE harus urut dari kiri ke kanan sesuai dengan urutan kolom saat indeks dibuat.

Misalnya, kueri-kueri di bawah ini akan memakai indeks:

SELECT * FROM table_name
WHERE col_1 = 'val_1' AND col_2 = 'val_2' AND col_3 = 'val_3';

atau

SELECT * FROM table_name
WHERE col_1 = 'val_1' AND col_2 = 'val_2';

atau

SELECT * FROM table_name
WHERE col_1 = 'val_1';

Kalau kueri-kueri di bawah ini tidak memakai indeks karena operasi pembandingan pertama tidak membandingkan kolom pertama:

SELECT * FROM table_name
WHERE col_3 = 'val_3';

atau

SELECT * FROM table_name
WHERE col_2 = 'val_2' AND col_3 = 'val_3';