Optimasi SQL untuk kontrol real-time dan daftar lagu yang membesar bukan sekadar menambah indeks lalu berharap semuanya cepat. Dalam aplikasi karaoke/web real-time, masalah biasanya muncul ketika banyak ponsel melakukan pencarian lagu, menambah request ke antrean, melihat status giliran, dan membaca riwayat pemutaran secara bersamaan.

Jika gejalanya adalah halaman daftar lagu makin lambat, pencarian terasa berat, atau refresh antrean menunda respons remote, fokus pertama bukan mengganti database, tetapi memahami query mana yang mahal, mengapa planner memilih jalur tertentu, dan bagaimana desain indeks memengaruhi pola baca-tulis. Artikel ini membahas pendekatan praktis untuk beban seperti itu.

Masalah nyata pada aplikasi karaoke real-time

Pola akses data pada aplikasi karaoke berbeda dari CRUD biasa. Beberapa tabel tumbuh cepat dan dibaca berulang-ulang:

  • songs: katalog lagu besar, sering dicari dan diurutkan.
  • song_requests: antrean request per room/session, sering ditambah, dibaca, dan diubah statusnya.
  • play_history: riwayat pemutaran yang terus membesar.
  • rooms / sessions: konteks kontrol real-time, biasanya kecil tetapi sering diakses.

Masalah umum yang muncul:

  • Query daftar lagu memakai OFFSET besar sehingga makin lambat di halaman belakang.
  • Pencarian lagu memakai LIKE '%term%' tanpa strategi indeks yang cocok.
  • Antrean request dibaca dengan filter dan urutan yang tidak didukung indeks gabungan.
  • Riwayat pemutaran bercampur dengan data aktif sehingga query operasional ikut melambat.
  • Terlalu banyak indeks mempercepat baca, tetapi memperlambat insert/update pada tabel yang sangat aktif.

Contoh skema yang realistis

Skema berikut sengaja sederhana, tetapi cukup dekat dengan kebutuhan nyata.

CREATE TABLE songs (
  id BIGINT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  artist_name VARCHAR(255) NOT NULL,
  language_code VARCHAR(10),
  popularity_score INT DEFAULT 0,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL
);

CREATE TABLE song_requests (
  id BIGINT PRIMARY KEY,
  room_id BIGINT NOT NULL,
  song_id BIGINT NOT NULL,
  requested_by VARCHAR(100),
  status VARCHAR(20) NOT NULL, -- queued, playing, done, cancelled
  priority INT NOT NULL DEFAULT 0,
  requested_at TIMESTAMP NOT NULL,
  started_at TIMESTAMP NULL,
  finished_at TIMESTAMP NULL
);

CREATE TABLE play_history (
  id BIGINT PRIMARY KEY,
  room_id BIGINT NOT NULL,
  song_id BIGINT NOT NULL,
  played_at TIMESTAMP NOT NULL,
  singer_name VARCHAR(100),
  request_id BIGINT NULL
);

Dari sini, kita bisa turunkan pola query yang paling kritis.

Mulai dari bottleneck, bukan dari asumsi

Gunakan EXPLAIN dan EXPLAIN ANALYZE

Langkah pertama adalah mengambil query yang benar-benar lambat dari log aplikasi atau slow query log, lalu cek rencana eksekusinya. Secara umum:

  • EXPLAIN menunjukkan rencana yang dipilih optimizer.
  • EXPLAIN ANALYZE menjalankan query dan menunjukkan biaya nyata, jumlah baris aktual, serta node yang paling mahal.

Contoh query daftar lagu yang terlihat normal tetapi bisa mahal:

SELECT id, title, artist_name
FROM songs
WHERE is_active = TRUE
  AND language_code = 'id'
ORDER BY popularity_score DESC, id DESC
LIMIT 20 OFFSET 10000;

Hal yang biasanya perlu diperhatikan saat membaca hasil EXPLAIN/ANALYZE:

  • Apakah terjadi full table scan atau sequential scan pada tabel besar.
  • Apakah database melakukan sort besar karena urutan tidak didukung indeks.
  • Apakah jumlah baris yang dipindai jauh lebih besar daripada yang dikembalikan.
  • Apakah estimasi planner meleset jauh dari nilai aktual, yang bisa menandakan statistik tidak akurat.

Jika query hanya mengambil 20 baris tetapi harus memindai puluhan ribu baris lalu mengurutkannya, masalah utamanya biasanya ada pada kombinasi filter, sort, dan pagination.

Pertanyaan audit yang membantu

  • Query ini dipanggil seberapa sering per detik?
  • Apakah query berjalan di jalur request real-time atau hanya di halaman admin?
  • Apakah latency tinggi berasal dari scan, sort, join, lock, atau network?
  • Bisakah query dipecah menjadi jalur data aktif dan arsip?

Memilih indeks untuk filter + sort

Indeks yang efektif harus mengikuti pola WHERE + ORDER BY yang benar-benar dipakai aplikasi. Menambah indeks per kolom secara terpisah sering tidak cukup.

Kasus 1: daftar lagu aktif per bahasa, diurutkan popularitas

Query:

SELECT id, title, artist_name
FROM songs
WHERE is_active = TRUE
  AND language_code = 'id'
ORDER BY popularity_score DESC, id DESC
LIMIT 20;

Indeks yang lebih cocok:

CREATE INDEX idx_songs_active_lang_popularity_id
ON songs (is_active, language_code, popularity_score DESC, id DESC);

Mengapa ini membantu:

  • Database bisa mempersempit kandidat berdasarkan is_active dan language_code.
  • Urutan hasil sudah sejalan dengan indeks, sehingga biaya sort bisa berkurang atau hilang.
  • id di akhir membantu menghasilkan urutan stabil jika ada nilai popularity_score yang sama.

Catatan trade-off: setiap indeks tambahan menambah biaya saat insert/update. Jika popularity_score sering berubah, indeks ini juga ikut sering diperbarui.

Kasus 2: membaca antrean request aktif per room

Query umum:

SELECT id, song_id, status, priority, requested_at
FROM song_requests
WHERE room_id = 42
  AND status = 'queued'
ORDER BY priority DESC, requested_at ASC, id ASC
LIMIT 30;

Indeks yang cocok:

CREATE INDEX idx_requests_room_status_priority_time
ON song_requests (room_id, status, priority DESC, requested_at ASC, id ASC);

Ini penting untuk skenario real-time karena ponsel klien cenderung sering memuat antrean room yang sama. Tanpa indeks gabungan, database bisa membaca terlalu banyak request dari room tersebut lalu mengurutkannya di memori.

Kesalahan umum saat membuat indeks

  • Mengindeks tiap kolom secara terpisah, padahal query butuh indeks gabungan.
  • Menaruh kolom urutan di depan padahal filter selektif ada di belakang.
  • Membuat terlalu banyak indeks “untuk berjaga-jaga”.
  • Tidak mengecek apakah query benar-benar menggunakan indeks yang dibuat.

Pencarian lagu: jangan samakan semua jenis search

Pencarian katalog lagu sering menjadi sumber beban terbesar. Namun strategi optimasinya tergantung jenis pencarian.

Pencarian prefix lebih mudah diindeks

Jika UI memang bisa diarahkan ke pencarian awalan, query seperti ini lebih ramah indeks:

SELECT id, title, artist_name
FROM songs
WHERE is_active = TRUE
  AND title LIKE 'cinta%'
ORDER BY title ASC, id ASC
LIMIT 20;

Dengan indeks yang sesuai pada kolom pencarian dan urutan, database punya peluang lebih baik memakai indeks.

Pencarian substring lebih mahal

Query seperti:

SELECT id, title, artist_name
FROM songs
WHERE is_active = TRUE
  AND (title LIKE '%cinta%' OR artist_name LIKE '%cinta%')
ORDER BY popularity_score DESC, id DESC
LIMIT 20;

sering tidak efisien pada indeks B-tree biasa karena wildcard di depan membuat pencarian tidak bisa memanfaatkan urutan indeks secara langsung. Untuk kasus seperti ini, pilihannya biasanya:

  • Batasi fitur menjadi prefix search jika kebutuhan produk memungkinkan.
  • Gunakan mesin pencarian atau fitur indeks teks yang sesuai kemampuan database Anda.
  • Pisahkan jalur pencarian dari jalur daftar lagu biasa agar query katalog utama tetap ringan.

Yang penting: jangan memaksa satu query SQL generik menangani semua kebutuhan search, ranking, dan sorting sekaligus jika pola aksesnya memang berbeda.

Hindari OFFSET besar dengan keyset pagination

OFFSET besar adalah penyebab klasik performa turun saat daftar lagu terus membesar. Secara logika, database tetap harus melewati banyak baris sebelum sampai ke halaman yang diminta.

Sebelum: OFFSET pagination

SELECT id, title, artist_name, popularity_score
FROM songs
WHERE is_active = TRUE
ORDER BY popularity_score DESC, id DESC
LIMIT 20 OFFSET 10000;

Masalahnya, halaman ke-500 jauh lebih mahal daripada halaman pertama.

Sesudah: keyset pagination

Gunakan nilai item terakhir dari halaman sebelumnya sebagai cursor.

SELECT id, title, artist_name, popularity_score
FROM songs
WHERE is_active = TRUE
  AND (
    popularity_score < 850
    OR (popularity_score = 850 AND id < 12345)
  )
ORDER BY popularity_score DESC, id DESC
LIMIT 20;

Mengapa lebih cepat:

  • Database tidak perlu “melompati” ribuan baris.
  • Query tetap bergerak maju mengikuti urutan indeks.
  • Latency lebih stabil walau data bertambah besar.

Kapan keyset pagination cocok

  • Infinite scroll atau tombol “load more”.
  • Daftar lagu populer, antrean request, riwayat terbaru.
  • UI yang tidak butuh lompat bebas ke halaman 237.

Trade-off keyset pagination

  • Tidak ideal untuk fitur “go to page N”.
  • Harus punya urutan yang stabil dan unik, biasanya menambahkan id sebagai tie-breaker.
  • Cursor perlu dibawa oleh klien atau API.

Strategi untuk tabel yang tumbuh cepat

Pada sistem karaoke, tabel aktif dan tabel historis biasanya tidak punya karakteristik yang sama. Jika semuanya dicampur tanpa strategi, query operasional akan ikut menanggung beban data lama.

Pisahkan data aktif dan arsip secara logis

Contoh pendekatan:

  • song_requests hanya menyimpan request aktif dan yang baru selesai dalam jangka pendek.
  • Request lama dipindahkan ke tabel arsip atau direpresentasikan di play_history.
  • UI kontrol real-time hanya membaca tabel aktif.

Keuntungan:

  • Indeks pada tabel aktif tetap kecil.
  • Query room yang sering dipakai menjadi lebih murah.
  • Vacuum/maintenance pada tabel aktif lebih ringan.

Trade-off:

  • Arsitektur query menjadi lebih kompleks.
  • Butuh proses arsip yang aman dan terjadwal.
  • Laporan historis mungkin perlu membaca beberapa sumber data.

Index hanya untuk pola akses aktif

Jangan mengindeks semua kemungkinan laporan pada tabel yang sangat sering ditulis. Untuk song_requests, prioritaskan indeks yang mendukung:

  • baca antrean aktif per room,
  • ambil item berikutnya untuk diputar,
  • ubah status request tertentu.

Laporan historis bisa dipindahkan ke tabel lain atau dijalankan secara asinkron.

Partisi atau arsip periodik

Untuk play_history yang tumbuh terus, dua pendekatan umum adalah:

  • arsip periodik: pindahkan data lama ke tabel arsip,
  • partitioning: pecah tabel berdasarkan rentang waktu atau kunci tertentu jika database Anda mendukung dan tim siap mengelolanya.

Pilih partitioning jika volume benar-benar besar dan pola query historis memang memanfaatkannya. Jika belum, arsip periodik sering lebih sederhana dan cukup efektif.

Contoh optimasi sebelum dan sesudah

1) Daftar lagu per bahasa

Sebelum:

SELECT id, title, artist_name
FROM songs
WHERE language_code = 'id'
ORDER BY popularity_score DESC
LIMIT 20 OFFSET 5000;

Masalah:

  • Tidak ada filter is_active jika memang dibutuhkan.
  • OFFSET besar.
  • Urutan bisa tidak stabil jika skor sama.

Sesudah:

SELECT id, title, artist_name, popularity_score
FROM songs
WHERE is_active = TRUE
  AND language_code = 'id'
  AND (
    popularity_score < 850
    OR (popularity_score = 850 AND id < 12345)
  )
ORDER BY popularity_score DESC, id DESC
LIMIT 20;

Dengan indeks:

CREATE INDEX idx_songs_active_lang_popularity_id
ON songs (is_active, language_code, popularity_score DESC, id DESC);

2) Ambil antrean aktif room

Sebelum:

SELECT *
FROM song_requests
WHERE room_id = 42
  AND status IN ('queued', 'playing')
ORDER BY requested_at ASC;

Masalah:

  • SELECT * mengambil kolom berlebih.
  • Urutan tidak sesuai logika prioritas jika ada fitur prioritas request.
  • Status gabungan bisa membuat kebutuhan indeks berbeda dari query yang paling sering.

Sesudah:

SELECT id, song_id, status, priority, requested_at
FROM song_requests
WHERE room_id = 42
  AND status = 'queued'
ORDER BY priority DESC, requested_at ASC, id ASC
LIMIT 30;

Dengan indeks:

CREATE INDEX idx_requests_room_status_priority_time
ON song_requests (room_id, status, priority DESC, requested_at ASC, id ASC);

Untuk item yang sedang diputar, lebih baik query terpisah jika pola aksesnya memang berbeda.

3) Riwayat pemutaran terbaru

Sebelum:

SELECT id, room_id, song_id, played_at
FROM play_history
ORDER BY played_at DESC
LIMIT 50;

Jika kebutuhan sebenarnya per room, query global ini terlalu luas.

Sesudah:

SELECT id, song_id, played_at, singer_name
FROM play_history
WHERE room_id = 42
ORDER BY played_at DESC, id DESC
LIMIT 50;

Dengan indeks:

CREATE INDEX idx_history_room_played_id
ON play_history (room_id, played_at DESC, id DESC);

Write cost vs read speed: trade-off yang harus dihitung

Pada aplikasi remote real-time, godaan terbesar adalah menambah banyak indeks demi semua tampilan terasa cepat. Namun setiap indeks punya biaya:

  • INSERT lebih mahal karena indeks harus diperbarui.
  • UPDATE pada kolom terindeks bisa memicu pemeliharaan indeks tambahan.
  • Storage bertambah.
  • Vacuum/maintenance bisa lebih berat tergantung engine database.

Karena itu, tentukan prioritas:

  1. Optimalkan query yang ada di jalur interaktif pengguna.
  2. Pisahkan query analitik/laporan dari jalur operasional.
  3. Hapus indeks yang tidak pernah dipakai atau tumpang tindih.

Rule of thumb yang aman: indeks mahal itu masuk akal jika query tersebut sering dipanggil dan benar-benar memengaruhi pengalaman real-time.

Tips debugging saat query masih lambat

  • Cek cardinality dan statistik: planner bisa salah memilih rencana jika statistik usang.
  • Periksa sort dan temp space: query bisa lambat karena pengurutan besar, bukan karena scan saja.
  • Lihat kolom yang diambil: hindari SELECT * pada endpoint yang sering dipanggil.
  • Audit join yang tidak perlu: kadang daftar lagu bisa dipecah jadi dua query lebih ringan daripada satu query kompleks.
  • Uji dengan data yang realistis: query yang cepat di staging kecil sering gagal di produksi besar.
  • Amati lock/contention: pada tabel request aktif, latency bisa datang dari transaksi saling menunggu, bukan murni dari rencana query.

Checklist audit performa SQL untuk kasus nyata

  1. Ambil 5-10 query paling lambat dari jalur daftar lagu, search, antrean, dan history.
  2. Jalankan EXPLAIN atau EXPLAIN ANALYZE pada query asli, bukan versi yang sudah disederhanakan.
  3. Catat apakah bottleneck utama berasal dari scan, sort, join, atau lock.
  4. Cocokkan WHERE dan ORDER BY dengan indeks gabungan yang relevan.
  5. Pastikan urutan hasil stabil dengan tie-breaker seperti id.
  6. Ganti pagination berbasis OFFSET besar menjadi keyset pagination bila UI memungkinkan.
  7. Bedakan query data aktif dan query data historis.
  8. Kurangi SELECT *; ambil hanya kolom yang dibutuhkan endpoint.
  9. Evaluasi apakah pencarian substring memang perlu SQL biasa atau butuh solusi search terpisah.
  10. Review biaya tulis dari setiap indeks baru sebelum diterapkan ke tabel yang sangat aktif.
  11. Uji ulang dengan volume data dan pola akses yang mendekati produksi.

Penutup

Optimasi SQL untuk kontrol real-time dan daftar lagu yang membesar paling efektif jika dimulai dari query nyata: daftar lagu, pencarian, antrean request, dan riwayat pemutaran. Fokus utamanya adalah membaca rencana eksekusi dengan benar, memilih indeks yang sesuai pola filter+sort, menghindari OFFSET besar dengan keyset pagination, dan mencegah tabel aktif terbebani data historis yang terus tumbuh.

Jika Anda hanya mengingat empat hal dari artikel ini, ingatlah ini: ukur dengan EXPLAIN/ANALYZE, buat indeks sesuai query nyata, hindari OFFSET besar, dan pisahkan data aktif dari arsip. Pada aplikasi karaoke real-time, empat langkah itu biasanya memberi dampak lebih besar daripada optimasi yang rumit tetapi tidak menyentuh bottleneck sebenarnya.