Mengoptimalkan Query Lambat

Query yang memakan waktu beberapa detik di ruang rapat biasanya hanya butuh beberapa menit untuk diperbaiki — asalkan diperbaiki dengan cara yang benar. Melompat langsung menambah index atau menulis ulang query berdasarkan firasat sering kali menghasilkan perubahan tanpa efek, atau lebih buruk: memperlambat operasi tulis tanpa disadari. Bab ini merangkum seluruh materi section ini menjadi satu metode kerja yang bisa diulang, lalu mempraktikkannya pada dua kasus nyata dengan data 200.000 baris.

Metode Lima Langkah #

Setiap optimasi query di SQLite mengikuti putaran yang sama:

Ukur dulu — nyalakan .timer on di sqlite3 CLI, atau ukur dari sisi aplikasi. Tanpa angka awal, kita tidak tahu apakah masalahnya nyata.
Baca rencananya — jalankan EXPLAIN QUERY PLAN pada query yang sama. SCAN pada tabel besar, USE TEMP B-TREE, atau subquery berkorelasi adalah petunjuk utama.
Rumuskan hipotesis — satu kalimat: “query lambat karena X, sehingga Y harus diubah menjadi Z”.
Perbaiki satu hal — tambahkan index, tulis ulang query, atau ubah skema. Satu perubahan per putaran agar efeknya jelas.
Ukur ulang dan bandingkan rencana — angka waktu berbeda-beda antar mesin, tetapi perubahan SCAN menjadi SEARCH adalah bukti permanen.

Studi Kasus 1: Subquery Berkorelasi Tanpa Index #

Skenarionya: laporan yang menjumlahkan seluruh pesanan milik 500 pelanggan pertama. Datanya cukup besar agar lambatnya terasa:

sql
CREATE TABLE pelanggan (
  id INTEGER PRIMARY KEY,
  nama TEXT NOT NULL
);
CREATE TABLE pesanan (
  id INTEGER PRIMARY KEY,
  pelanggan_id INTEGER NOT NULL,
  total INTEGER NOT NULL,
  tanggal TEXT NOT NULL
);

WITH RECURSIVE angka(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1 FROM angka WHERE x < 5000
)
INSERT INTO pelanggan (nama)
SELECT 'Pelanggan ' || printf('%04d', x)
FROM angka;

WITH RECURSIVE angka(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1 FROM angka WHERE x < 200000
)
INSERT INTO pesanan (pelanggan_id, total, tanggal)
SELECT (x % 5000) + 1,
       (x * 53) % 1000000 + 5000,
       printf('2025-%02d-%02d', ((x % 12) + 1), ((x % 28) + 1))
FROM angka;

SELECT count(*) FROM pelanggan;
SELECT count(*) FROM pesanan;

Output yang Dihasilkan:

text

5000
200000

Query laporan versi pertama memakai subquery berkorelasi; pembungkus sum(...) memaksa semua 500 baris dievaluasi sekaligus memberi hasil satu angka:

sql
SELECT sum(jumlah) AS total_semua_pesanan FROM (
  SELECT (SELECT count(*) FROM pesanan s WHERE s.pelanggan_id = p.id) AS jumlah
  FROM pelanggan p
  WHERE p.id <= 500
);

Output yang Dihasilkan:

text

Langkah pertama metode: ukur. Di mesin penulis, query ini memakan waktu sekitar 3,5 detik. Langkah kedua: baca rencananya.

sql
EXPLAIN QUERY PLAN
SELECT sum(jumlah) AS total_semua_pesanan FROM (
  SELECT (SELECT count(*) FROM pesanan s WHERE s.pelanggan_id = p.id) AS jumlah
  FROM pelanggan p
  WHERE p.id <= 500
);

Output yang Dihasilkan:

text
QUERY PLAN
|--SEARCH p USING INTEGER PRIMARY KEY (rowid<?)
`--CORRELATED SCALAR SUBQUERY 1
   `--SCAN s

Mari kita bedah rencana di atas:

Baris pertama (SEARCH p USING INTEGER PRIMARY KEY): loop luar mengambil 500 pelanggan lewat rowid — tidak masalah.
Baris kedua (CORRELATED SCALAR SUBQUERY 1): subquery dijalankan sekali untuk setiap pelanggan — 500 kali.
Baris ketiga (SCAN s): setiap subquery memindai seluruh 200.000 baris pesanan karena kolom pelanggan_id tidak memiliki index.

Hipotesis: 500 kali 200.000 baris sama dengan 100 juta pemeriksaan baris. Perbaikannya bisa dari dua arah — tulis ulang query agar loop berat hilang, atau beri index pada kolom yang dipindai.

Perbaikan pertama, tulis ulang sebagai LEFT JOIN dengan GROUP BY:

sql
SELECT sum(jumlah) AS total_semua_pesanan FROM (
  SELECT count(s.id) AS jumlah
  FROM pelanggan p
  LEFT JOIN pesanan s ON s.pelanggan_id = p.id
  WHERE p.id <= 500
  GROUP BY p.id
);

Output yang Dihasilkan:

text

Di mesin penulis turun menjadi sekitar 0,09 detik. Rencananya menjelaskan kenapa: query planner kini bebas memilih urutan loop dan membangun index sementara untuk keperluannya sendiri:

text
QUERY PLAN
|--CO-ROUTINE (subquery-1)
|  |--SEARCH p USING INTEGER PRIMARY KEY (rowid<?)
|  |--BLOOM FILTER ON s (pelanggan_id=?)
|  `--SEARCH s USING AUTOMATIC COVERING INDEX (pelanggan_id=?) LEFT-JOIN
`--SCAN (subquery-1)

Label AUTOMATIC COVERING INDEX berarti engine SQLite membangun index sementara di memori khusus untuk query ini — membantu, tetapi dibangun ulang setiap eksekusi. Perbaikan kedua memberi index permanen pada kolom foreign key:

sql
CREATE INDEX idx_pesanan_pelanggan ON pesanan(pelanggan_id);

Query asli (versi subquery berkorelasi) kembali memakan waktu di bawah 1 milidetik di mesin penulis, dan rencananya bersih:

sql
EXPLAIN QUERY PLAN
SELECT sum(jumlah) AS total_semua_pesanan FROM (
  SELECT (SELECT count(*) FROM pesanan s WHERE s.pelanggan_id = p.id) AS jumlah
  FROM pelanggan p
  WHERE p.id <= 500
);

Output yang Dihasilkan:

text
QUERY PLAN
|--SEARCH p USING INTEGER PRIMARY KEY (rowid<?)
`--CORRELATED SCALAR SUBQUERY 1
   `--SEARCH s USING COVERING INDEX idx_pesanan_pelanggan (pelanggan_id=?)

Studi Kasus 2: Top-N dengan ORDER BY dan LIMIT #

Kasus kedua: dasbor menampilkan sepuluh pesanan dengan nilai terbesar.

sql
EXPLAIN QUERY PLAN
SELECT id, total FROM pesanan
ORDER BY total DESC
LIMIT 10;

Output yang Dihasilkan:

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

Rencananya menunjukkan pemborosan yang halus: engine SQLite memindai 200.000 baris dan mengurutkan semuanya di TEMP B-TREE, padahal hanya 10 baris teratas yang diambil. Di mesin penulis sekitar 11 milidetik — masih ringan, tetapi tumbuh linear mengikuti jumlah baris. Index pada kolom pengurutan membuat baris bisa dibaca 10 kali dari puncak B-tree, tanpa pengurutan sama sekali:

sql
CREATE INDEX idx_pesanan_total ON pesanan(total);

EXPLAIN QUERY PLAN
SELECT id, total FROM pesanan
ORDER BY total DESC
LIMIT 10;

Output yang Dihasilkan:

text

QUERY PLAN
`--SCAN pesanan USING COVERING INDEX idx_pesanan_total

sql
SELECT id, total FROM pesanan
ORDER BY total DESC
LIMIT 10;

Output yang Dihasilkan:

text
188679|1004987
169811|1004983
150943|1004979
132075|1004975
113207|1004971
94339|1004967
75471|1004963
56603|1004959
37735|1004955
18867|1004951

Di mesin penulis turun dari sekitar 11 milidetik menjadi di bawah 0,1 milidetik — ratusan kali lebih cepat untuk satu index.

Daftar Penyebab Umum #

Dari dua studi kasus dan seluruh section ini, berikut gejala, penyebab, dan obatnya:

Gejala di EXPLAIN QUERY PLAN	Penyebab	Obat
`SCAN` pada tabel besar	Kolom filter tanpa index	`CREATE INDEX` pada kolom filter
`SCAN` meski kolom diindex	Fungsi di kolom, wildcard awal `LIKE`	Expression index, atau tulis ulang predikatnya
`USE TEMP B-TREE FOR ORDER BY`	Urutan hasil tidak didukung index	Index komposit dengan kolom urut
`CORRELATED SCALAR SUBQUERY` di loop besar	Subquery dijalankan per baris	Tulis ulang sebagai JOIN, atau index kolom join
Query lambat meski rencana tampak baik	Statistik usang	`ANALYZE` atau `PRAGMA optimize`

Contoh cepat untuk baris kedua: wildcard di awal pola memaksa scan penuh, dan tidak ada index biasa yang bisa menolong:

sql
EXPLAIN QUERY PLAN SELECT * FROM pesanan WHERE tanggal LIKE '%07%';

Output yang Dihasilkan:

text

QUERY PLAN
`--SCAN pesanan

Untuk pencarian teks bebas pada tabel besar, solusi yang tepat adalah FTS5 — dibahas pada chapter Full-Text Search dengan FTS5.

Kapan Tidak Perlu Optimasi #

Optimasi punya harga: index memperlambat tulisan, query yang digabung-gabung menjadi sulit dibaca, dan waktu kita sendiri terbatas. Tunda optimasi bila query berjalan pada tabel kecil (scan 1.000 baris selesai di bawah satu milidetik), query hanya dijalankan sekali sehari di jam sepi, atau hasilnya sudah cukup cepat bagi pengguna. Ukur dulu, putuskan kemudian — angka dari .timer on selalu lebih jujur daripada firasat.