Saat Indeks Tidak Bekerja — Pola dan Solusinya

Pola di mana indeks ada tapi tidak bekerja

Bahkan setelah kamu membuat indeks, cara penulisan kondisi tertentu menghentikannya dari digunakan dan kueri tetap berjalan sebagai full table scan (SCAN).

Tiga penyebab klasik ada di bawah.

• Kondisi yang membungkus kolom dalam fungsi atau aritmetika (substr(col) / upper(col) / col + 0, dan seterusnya)
• Pencocokan tengah LIKE '%x%'
• Kondisi negasi (<> / NOT IN)

Di setiap kasus penyebabnya sama: perbandingan tidak lagi sejajar dengan urutan yang disimpan di indeks.

Nanti di artikel kita melihat indeks ekspresi untuk kasus di mana kamu tidak bisa menghindari membungkus kolom dalam fungsi, dan indeks parsial untuk mempersempit indeks ke subset baris tertentu.

Sepanjang artikel ini kamu akan membandingkan "cara yang tidak memakai indeks" dengan "cara yang memakainya" berdampingan di query plan.

-- Bahkan dengan indeks pada amount,
-- membungkus kolom dalam fungsi jatuh kembali ke full table scan
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(amount);

-- Bandingkan kolom apa adanya → SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE amount >= 800000;

-- Bungkus kolom dalam fungsi → kembali ke SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE abs(amount) >= 800000;

Pertimbangkan kebutuhannya: "Saya ingin menghitung hanya penjualan dari 2024." Buat indeks pada sale_date, lalu bandingkan dua query plan berdampingan — satu yang membungkus kolom dalam fungsi, dan satu yang memakai kondisi rentang dengan kolom apa adanya — dan perhatikan bagaimana SCAN versus SEARCH berubah. Bangun ulang indeks di dalam satu eksekusi konsol ini supaya plan sepenuhnya mandiri. (Jalankan dengan benar untuk memunculkan penjelasan.)

① Buang indeks yang ada dengan DROP INDEX IF EXISTS dan buat indeks satu-kolom pada sale_date.

② Pakai EXPLAIN QUERY PLAN pada kueri yang menarik tahun dari sale_date dengan substr dan membandingkannya dengan '2024' (bentuk yang membungkus kolom dalam fungsi).

③ Lalu tampilkan plan untuk kueri yang mengungkapkan makna yang sama sebagai kondisi rentang pada sale_date (lebih besar atau sama dengan awal 2024, lebih kecil dari awal tahun berikutnya) dan bandingkan dengan ②.

LIKE pencocokan tengah

Pencocokan prefix LIKE (bentuk yang awalnya tetap, seperti 'P00%') pada prinsipnya bisa mempersempit rentang memakai urutan indeks.

Sebaliknya, pencocokan tengah ('%5%') atau pencocokan suffix ('%50') tidak punya awal yang tetap, jadi tidak ada database yang bisa menelusuri indeks dan kueri menjadi full table scan.

Meskipun begitu, di SQLite, `LIKE` case-insensitive secara default, jadi ia tidak sejajar dengan indeks berurutan BINARY yang biasa — bahkan pencocokan prefix LIKE tidak mendapat SEARCH dan jatuh kembali ke SCAN (lihat callout di bawah).

Jadi saat kamu ingin pencocokan prefix di SQLite memakai lookup indeks, solusi sederhananya adalah menulis ulang sebagai kondisi rentang, seperti product >= 'P00' AND product < 'P01'.

Untuk LIKE pencocokan tengah, kalau kamu benar-benar membutuhkannya sebagai kebutuhan, pertimbangkan mekanisme berbeda seperti full-text search.

LIKE SQLite case-insensitive secara default

Di SQLite, PRAGMA case_sensitive_like adalah OFF secara default, jadi LIKE adalah perbandingan case-insensitive.

Indeks dibangun memakai urutan BINARY standar (urutan kamus case-sensitive), jadi bahkan pencocokan prefix seperti LIKE 'P00%' tidak sejajar dengan urutan indeks dan aturan perbandingan — ia tidak dipakai sebagai SEARCH.

Kalau kamu menjalankan LIKE 'P00%' di konsol ini, plan menjadi SCAN perf_sales USING COVERING INDEX.

Solusi sederhana adalah menulis ulang sebagai kondisi rentang (product >= 'P00' AND product < 'P01'), atau menyebutkan variasi case di sisi nilai (product IN ('P050', 'p050')).

Di artikel ini kita memakai opsi paling langsung — menulis ulang sebagai kondisi rentang.

Di MySQL / PostgreSQL, kalau LIKE dikonfigurasi case-sensitive, pencocokan prefix biasanya menjadi SEARCH.

-- Bandingkan plan kondisi rentang dan LIKE terhadap indeks pada product
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(product);

-- Kondisi rentang → awal tetap, jadi SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales
WHERE product >= 'P15' AND product < 'P16';

-- LIKE pencocokan prefix → SCAN di SQLite (case-insensitive)
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE product LIKE 'P15%';

-- LIKE pencocokan tengah → SCAN di DB manapun
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE product LIKE '%9%';

Ambil kebutuhan "Saya ingin menghitung penjualan yang kode produknya dimulai dengan prefix tertentu" dan bandingkan plan untuk tiga bentuk: (A) kondisi rentang, (B) LIKE pencocokan prefix, dan (C) LIKE pencocokan tengah. Buat indeks pada product dan perhatikan bentuk mana yang menjadi SEARCH di SQLite.

① Buang indeks yang ada dengan DROP INDEX IF EXISTS dan buat indeks satu-kolom pada product.

② Pakai EXPLAIN QUERY PLAN pada kueri yang menghitung baris dengan kondisi rentang WHERE product >= 'P00' AND product < 'P01'.

③ Tampilkan plan untuk makna yang sama ditulis sebagai LIKE pencocokan prefix 'P00%' dan bandingkan dengan ②.

④ Lalu tampilkan juga plan untuk LIKE pencocokan tengah '%5%'.

Membungkus kolom dengan upper() atau aritmetika menghentikan indeks dari digunakan

Saat kamu menerapkan fungsi atau aritmetika ke kolom untuk memfilternya, indeks kolom tidak bisa dipakai meskipun ada.

Alasannya adalah indeks diurutkan oleh nilai mentah kolom, jadi perbandingan terhadap nilai yang ditransformasi seperti upper(product) atau amount + 10000 tidak lagi sejajar dengan urutan indeks.

Misalnya, WHERE upper(product) = 'P050' harus melewatkan product setiap baris melalui upper() sebelum membandingkan, jadi indeks pada product tidak dipakai dan kueri jatuh kembali ke full scan.

Demikian pula, WHERE amount + 10000 = 900000 harus menghitung amount + 10000 untuk setiap baris sebelum membandingkan, jadi indeks pada amount tidak dipakai.

Solusinya adalah biarkan kolom apa adanya dan sesuaikan di sisi nilai.

upper(product) = 'P050', kalau nilai yang diharapkan hanya 'P050' dan 'p050', menjadi WHERE product IN ('P050', 'p050') — dan IN memungkinkan indeks mempersempit beberapa nilai sekaligus.

amount + 10000 = 900000 bisa ditulis ulang langsung sebagai amount = 800000 dengan mengurangi 10000 dari kedua sisi, dan indeks pada amount mempersempitnya dalam satu langkah.

Menulis ulang substr(sale_date, 1, 4) sebagai kondisi rentang di bagian sebelumnya adalah ide "jangan transformasi kolom" yang sama diterapkan ke tanggal.

-- Contoh membungkus kolom dengan lower() terhadap indeks pada product
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(product);

-- Bungkus kolom dengan lower() → SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE lower(product) = 'p001';

-- Sebutkan variasi case di sisi nilai → SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE product IN ('P001', 'p001');

-- Contoh pembungkusan aritmetika (terhadap indeks pada qty)
DROP INDEX IF EXISTS ix_qty;
CREATE INDEX ix_qty ON perf_sales(qty);

-- Bungkus dengan qty * 2 → SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE qty * 2 = 20;

-- Sederhanakan ekspresi di sisi nilai (bagi kedua sisi dengan 2) → SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE qty = 10;

Pertimbangkan kebutuhannya: "Saya ingin menghitung penjualan untuk kode produk 'P050' (atau bentuk huruf kecilnya 'p050')."

Buat indeks pada product dan bandingkan plan untuk dua bentuk — membungkus kolom dengan upper() versus menyebutkan nilai kandidat dengan IN (...) di sisi nilai.

Kedua bentuk menargetkan perbandingan yang sama yang mencakup 'P050' dan 'p050', jadi hasilnya identik.

① Buang indeks yang ada dengan DROP INDEX IF EXISTS dan buat indeks satu-kolom pada product.

② Pakai EXPLAIN QUERY PLAN pada kueri yang membungkus kolom dengan upper() seperti WHERE upper(product) = 'P050'.

③ Tampilkan plan untuk makna yang sama ditulis sebagai WHERE product IN ('P050', 'p050') dan bandingkan dengan ②.

Untuk kebutuhan "Saya ingin menghitung penjualan dengan amount tepat 800.000", buat indeks pada amount dan bandingkan plan untuk dua bentuk — menerapkan aritmetika ke kolom versus menyesuaikan di sisi nilai.

amount + 10000 = 900000 menjadi amount = 800000 setelah kamu mengurangi 10000 dari kedua sisi — secara matematis sepenuhnya setara.

① Buang indeks yang ada dengan DROP INDEX IF EXISTS dan buat indeks satu-kolom pada amount.

② Pakai EXPLAIN QUERY PLAN pada kueri yang menerapkan aritmetika ke kolom seperti WHERE amount + 10000 = 900000.

③ Tampilkan plan untuk makna yang sama ditulis sebagai WHERE amount = 800000 dan bandingkan dengan ②.

Kondisi negasi (<>, NOT IN)

Kondisi negasi seperti <> (tidak sama dengan) atau NOT IN sering kali berakhir menargetkan "sebagian besar tabel", jadi tidak ada gunanya mempersempit dengan indeks.

Misalnya, saat status adalah 'paid' (sekitar 40.000 dari 50.000 baris) / 'pending' (sekitar 5.000) / 'refunded' (sekitar 5.000), WHERE status <> 'paid' menargetkan 10.000 baris — sekitar 20% dari tabel.

Selektivitasnya tidak cukup rendah, jadi optimizer memutuskan full table scan lebih murah.

Solusinya adalah menulis ulang sebagai kondisi kesamaan.

WHERE status <> 'paid' bisa ditulis ulang sebagai WHERE status IN ('pending', 'refunded'), dan bentuk itu bisa mempersempit baris target lewat indeks.

-- Bandingkan penulisan ulang NOT IN vs IN terhadap indeks pada status
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(status);

-- Kondisi negasi (NOT IN) → target lebar, SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE status NOT IN ('refunded');

-- Ditulis ulang sebagai kesamaan (IN) → SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE status IN ('paid', 'pending');

Pertimbangkan kebutuhannya: "Saya ingin menghitung penjualan selain paid." Buat indeks pada status dan bandingkan plan antara menulisnya dengan <> dan menulis ulang makna yang sama sebagai kondisi kesamaan dengan IN.

① Buang indeks yang ada dengan DROP INDEX IF EXISTS dan buat indeks satu-kolom pada status.

② Pakai EXPLAIN QUERY PLAN pada kueri yang menghitung baris yang cocok dengan WHERE status <> 'paid'.

③ Tampilkan plan untuk makna yang sama ditulis ulang sebagai WHERE status IN ('pending', 'refunded') dan bandingkan dengan ②.

Perbaiki dengan indeks ekspresi dan indeks parsial

Saat membungkus kolom dalam fungsi tidak terhindarkan, indeks ekspresi (indeks yang dibangun pada hasil ekspresi, bukan kolom mentah) menyelamatkan hari.

Dengan CREATE INDEX ix ON perf_sales(substr(sale_date,1,4)), membuat indeks pada ekspresi yang sama dengan yang dipakai kondisi mengubah perbandingan pada ekspresi itu menjadi lookup indeks.

Syaratnya adalah ekspresi di kondisi cocok dengan ekspresi di indeks.

Alat lainnya adalah indeks parsial (indeks dengan klausa WHERE, dibangun hanya untuk subset baris tertentu).

Kalau baris status='pending' hanya bagian dari tabel (5.000 dari 50.000), kamu bisa membangun indeks yang dipersempit seperti CREATE INDEX ix ON perf_sales(emp_id) WHERE status='pending'.

Indeks itu sendiri tetap kecil dan secara efisien mempersempit kueri yang menyertakan kondisi itu.

-- Indeks ekspresi: bangun pada ekspresi yang sama dengan yang dipakai kondisi
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(substr(sale_date, 1, 4));

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales
WHERE substr(sale_date, 1, 4) = '2024';

Pertimbangkan kebutuhannya: "Saya ingin mencari penjualan in-progress (pending) yang dipersempit berdasarkan sales rep." Pending adalah subset kecil (5.000 dari 50.000), jadi bangun indeks parsial yang menargetkan hanya baris itu dan konfirmasi bahwa query plan yang menyertakan status='pending' menjadi lookup indeks.

① Buang indeks yang ada dengan DROP INDEX IF EXISTS.

② Pada emp_id, buat indeks parsial (memakai bentuk CREATE INDEX ... WHERE ...) yang menargetkan hanya baris di mana status sama dengan 'pending'.

③ Pakai EXPLAIN QUERY PLAN pada kueri yang menghitung baris di mana status adalah 'pending' dan emp_id tertentu, dan konfirmasi indeks parsial sedang dipakai.