Pada game puzzle harian, bottleneck database biasanya muncul bukan saat menulis skor, tetapi saat membaca data yang terus bertambah: menampilkan leaderboard harian, mencari skor terbaik per user, membuka riwayat permainan, dan memuat hasil terbaru dengan pagination. Jika query-query ini hanya mengandalkan primary key atau index tunggal yang salah urut, performa akan turun cepat ketika jumlah baris membesar.

Optimasi leaderboard harian dengan index SQL umumnya berhasil jika desain tabel dan pola query diselaraskan. Kuncinya adalah: pisahkan kebutuhan ranking dari histori mentah, gunakan composite index sesuai urutan filter dan sort, hindari OFFSET besar untuk data yang terus bertambah, lalu validasi keputusan dengan EXPLAIN sebelum dan sesudah perubahan.

Model data yang realistis untuk game puzzle harian

Misalkan aplikasi menyimpan satu baris untuk setiap sesi atau submit permainan. Data yang umum:

  • user_id: pemain
  • daily_seed: identitas puzzle harian
  • score: nilai akhir
  • attempts: jumlah percobaan
  • completed_at: waktu selesai
  • grid_state: state peta/grid terakhir atau hasil akhir
  • created_at: waktu insert

Skema sederhana:

CREATE TABLE game_results (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  daily_seed DATE NOT NULL,
  score INT NOT NULL,
  attempts INT NOT NULL,
  completed_at TIMESTAMP NULL,
  created_at TIMESTAMP NOT NULL,
  grid_state TEXT NOT NULL
);

Untuk PostgreSQL maupun MySQL secara umum, kolom besar seperti grid_state jangan dimasukkan ke index kecuali memang ada kebutuhan pencarian spesifik. Index pada kolom besar memperbesar ukuran index, menambah biaya write, dan jarang membantu query leaderboard.

Jika satu user bisa bermain beberapa kali pada seed yang sama, Anda perlu memutuskan apakah leaderboard dihitung dari:

  • semua submission, atau
  • best result per user per seed

Keputusan ini memengaruhi desain query dan index. Dalam kebanyakan game harian, yang dipakai adalah hasil terbaik per user untuk seed tersebut.

Query yang biasanya lambat dan penyebabnya

1. Leaderboard harian

Contoh kebutuhan:

SELECT user_id, score, attempts, completed_at
FROM game_results
WHERE daily_seed = '2026-08-30'
ORDER BY score DESC, attempts ASC, completed_at ASC
LIMIT 100;

Gejala lambat yang umum:

  • Database melakukan scan banyak baris untuk seed yang sama.
  • Hasil ORDER BY perlu di-sort terpisah karena urutan index tidak cocok.
  • Kolom filter ada di index, tetapi kolom sort tidak mengikuti urutan yang dibutuhkan.

2. Skor terbaik per user untuk satu seed

Contoh kebutuhan:

SELECT user_id, MAX(score) AS best_score
FROM game_results
WHERE daily_seed = '2026-08-30'
GROUP BY user_id;

Query agregasi seperti ini bisa mahal jika semua submission masih dihitung dari tabel mentah, terutama saat pemain sering retry. Dalam praktik, ini sering menjadi titik keputusan: tetap query langsung dari tabel hasil, atau simpan tabel ringkasan terpisah.

3. Riwayat permainan per user

SELECT daily_seed, score, attempts, completed_at
FROM game_results
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

Jika hanya ada index pada user_id, database tetap mungkin perlu mengambil semua baris user lalu melakukan sort. Saat histori tumbuh, ini jadi mahal.

4. Pagination hasil terbaru

SELECT id, user_id, daily_seed, score, created_at
FROM game_results
ORDER BY created_at DESC
LIMIT 50 OFFSET 50000;

OFFSET besar memaksa database melewati banyak baris sebelum mengembalikan hasil. Ini sering terlihat normal saat data kecil, lalu memburuk drastis saat data produksi tumbuh.

Composite index yang tepat untuk leaderboard dan histori

Aturan praktis yang aman:

  1. Kolom pada WHERE biasanya diletakkan lebih dulu.
  2. Setelah itu, kolom untuk ORDER BY mengikuti urutan akses.
  3. Jangan membuat terlalu banyak index yang mirip; setiap index menambah biaya insert dan update.

Index untuk leaderboard harian

Jika query utama adalah:

SELECT user_id, score, attempts, completed_at
FROM game_results
WHERE daily_seed = ?
ORDER BY score DESC, attempts ASC, completed_at ASC
LIMIT 100;

Maka kandidat index yang masuk akal:

CREATE INDEX idx_results_seed_rank
ON game_results (daily_seed, score DESC, attempts ASC, completed_at ASC, user_id);

Mengapa urutan ini bekerja:

  • daily_seed membatasi ruang pencarian ke satu puzzle harian.
  • Baris di dalam seed sudah tersusun sesuai ranking.
  • LIMIT 100 memungkinkan database berhenti lebih cepat tanpa sort besar.
  • user_id di akhir berguna bila kolom itu ikut dibaca, meski manfaat tepatnya bergantung engine dan rencana eksekusi.

Jika engine atau pola query Anda tidak selalu memanfaatkan arah ASC/DESC secara optimal, fokus utamanya tetap sama: kolom filter lebih dulu, lalu kolom sort. Validasi dengan EXPLAIN, jangan mengandalkan asumsi.

Index untuk riwayat permainan per user

CREATE INDEX idx_results_user_created
ON game_results (user_id, created_at DESC, id DESC);

Index ini cocok untuk:

SELECT id, daily_seed, score, attempts, created_at
FROM game_results
WHERE user_id = ?
ORDER BY created_at DESC, id DESC
LIMIT 20;

Penambahan id membantu membuat urutan stabil saat ada banyak baris dengan created_at sama. Ini penting untuk pagination yang konsisten.

Index untuk pencarian best score per user

Jika Anda masih menghitung dari tabel mentah, index berikut biasanya lebih membantu daripada index tunggal:

CREATE INDEX idx_results_seed_user_score
ON game_results (daily_seed, user_id, score DESC, attempts ASC, completed_at ASC);

Namun ada trade-off penting: index ini bagus untuk mengelompokkan hasil per user dalam satu seed, tetapi bisa tumpang tindih dengan index leaderboard. Jangan otomatis membuat semua kombinasi. Pilih berdasarkan query yang paling sering dan paling mahal.

Kapan perlu tabel ringkasan terpisah

Jika satu user bisa punya banyak submission per seed, leaderboard terbaik per user sering lebih efisien jika disimpan dalam tabel tersendiri, misalnya daily_best_results:

CREATE TABLE daily_best_results (
  daily_seed DATE NOT NULL,
  user_id BIGINT NOT NULL,
  score INT NOT NULL,
  attempts INT NOT NULL,
  completed_at TIMESTAMP NULL,
  PRIMARY KEY (daily_seed, user_id)
);

Lalu index untuk ranking:

CREATE INDEX idx_daily_best_rank
ON daily_best_results (daily_seed, score DESC, attempts ASC, completed_at ASC, user_id);

Keuntungannya:

  • Leaderboard membaca jauh lebih sedikit baris.
  • Agregasi MAX(score) per user tidak dihitung ulang terus-menerus.
  • Riwayat mentah dan ranking dipisahkan dengan jelas.

Kekurangannya:

  • Logika write lebih kompleks.
  • Perlu menjaga konsistensi saat user submit hasil baru.
  • Perlu strategi update yang idempoten bila ada retry dari aplikasi.

Sebelum dan sesudah optimasi query

Contoh leaderboard: sebelum

SELECT user_id, score, attempts, completed_at
FROM game_results
WHERE daily_seed = ?
ORDER BY score DESC, attempts ASC, completed_at ASC
LIMIT 100;

Masalahnya bukan pada SQL-nya, tetapi pada index yang tidak cocok. Misalnya hanya ada:

CREATE INDEX idx_results_seed ON game_results (daily_seed);

Akibatnya, database bisa saja:

  • menemukan semua baris dengan seed yang sama, lalu
  • melakukan sort terpisah untuk score, attempts, dan completed_at.

Sesudah

CREATE INDEX idx_results_seed_rank
ON game_results (daily_seed, score DESC, attempts ASC, completed_at ASC, user_id);

Dengan index ini, engine memiliki peluang jauh lebih besar untuk membaca baris sesuai urutan ranking dan berhenti setelah LIMIT terpenuhi.

Contoh riwayat user: sebelum

SELECT id, daily_seed, score, created_at
FROM game_results
WHERE user_id = ?
ORDER BY created_at DESC
LIMIT 20;

Jika hanya ada index pada created_at atau hanya pada user_id, performa tetap kurang baik karena filter dan sort tidak sejalan.

Sesudah

CREATE INDEX idx_results_user_created
ON game_results (user_id, created_at DESC, id DESC);

Ini membuat query histori lebih natural bagi engine: cari dulu baris milik user tertentu, lalu baca dari yang terbaru.

Offset vs keyset pagination untuk hasil terbaru

Masalah OFFSET pada tabel yang terus tumbuh

Pagination seperti ini mudah ditulis:

SELECT id, user_id, daily_seed, score, created_at
FROM game_results
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 50000;

Namun untuk halaman dalam, database tetap harus melewati banyak baris. Pada sistem aktif dengan insert terus-menerus, OFFSET besar juga bisa menghasilkan pengalaman yang tidak stabil: data bisa bergeser antar halaman.

Keyset pagination yang lebih efisien

Gunakan cursor berdasarkan kolom urutan terakhir yang sudah terlihat:

SELECT id, user_id, daily_seed, score, created_at
FROM game_results
WHERE (created_at < ?)
   OR (created_at = ? AND id < ?)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Index yang sesuai:

CREATE INDEX idx_results_created_id
ON game_results (created_at DESC, id DESC);

Kapan memilih masing-masing:

  • OFFSET: cukup untuk admin page kecil, dataset terbatas, atau kebutuhan lompat ke halaman tertentu.
  • Keyset: lebih cocok untuk feed hasil terbaru, histori panjang, dan data yang terus bertambah.

Trade-off keyset:

  • Tidak ideal untuk “langsung ke halaman 200”.
  • Aplikasi perlu menyimpan cursor dari baris terakhir.
  • Urutan harus stabil, sehingga kombinasi seperti created_at, id penting.

Cara membaca EXPLAIN secara praktis

EXPLAIN membantu memverifikasi apakah index yang dibuat benar-benar dipakai. Format dan istilahnya berbeda antara PostgreSQL dan MySQL, tetapi prinsip yang dicari serupa.

Yang ingin dilihat

  • Query menggunakan index scan/range scan, bukan full table scan untuk kasus selektif.
  • Filter dan sort sebisa mungkin terpenuhi oleh index.
  • Jumlah baris yang diproses masuk akal dibanding hasil akhir.
  • Tidak ada langkah sort besar padahal urutan bisa didorong ke index.

Contoh penggunaan

EXPLAIN
SELECT user_id, score, attempts, completed_at
FROM game_results
WHERE daily_seed = '2026-08-30'
ORDER BY score DESC, attempts ASC, completed_at ASC
LIMIT 100;

Interpretasi umum:

  • Jika planner menunjukkan scan berdasarkan index daily_seed saja lalu ada operasi sort tambahan, index belum ideal.
  • Jika planner menunjukkan akses index yang sudah mengikuti urutan ranking, itu pertanda lebih baik.
  • Jika estimasi jumlah baris sangat meleset dari kenyataan, statistik bisa usang.

Gejala index salah urut

  • Kolom WHERE ada di index, tetapi ORDER BY tetap memicu sort besar.
  • Query cepat tanpa ORDER BY, tetapi lambat saat sorting ditambahkan.
  • Index terlihat “mirip”, namun planner tidak memilihnya karena prefix kolom tidak sesuai pola filter.

Contoh salah urut:

CREATE INDEX idx_wrong
ON game_results (score DESC, daily_seed, attempts ASC);

Untuk query leaderboard harian, index ini kurang cocok karena engine lebih sulit membatasi data berdasarkan daily_seed terlebih dulu. Pada banyak kasus, hasilnya tetap berujung scan rentang yang lebih luas atau sort tambahan.

Tips debugging saat EXPLAIN mengecewakan

  • Periksa apakah urutan kolom index sesuai urutan akses nyata query.
  • Pastikan kondisi query konsisten; fungsi pada kolom terindeks bisa menghambat pemakaian index.
  • Hindari SELECT * pada query panas jika hanya perlu beberapa kolom.
  • Perbarui statistik database sesuai prosedur engine yang dipakai.
  • Uji query dengan parameter yang realistis, bukan hanya data kecil lokal.

Kesalahan umum pada desain index leaderboard

Terlalu banyak index mirip

Misalnya membuat semua kombinasi berikut sekaligus:

  • (daily_seed)
  • (daily_seed, score)
  • (daily_seed, score, attempts)
  • (daily_seed, user_id, score)

Ini memperbesar biaya write dan belum tentu membantu planner. Mulailah dari query termahal dan paling sering, lalu buat index minimum yang benar-benar menurunkan biaya baca.

Mengabaikan biaya tulis

Pada game aktif, setiap submit skor berarti insert. Setiap index tambahan harus diperbarui. Jika Anda menambah banyak index untuk semua kemungkinan laporan, write throughput akan turun.

Menyimpan grid_state di tabel panas tanpa pertimbangan

Jika grid_state besar dan sering dibaca padahal leaderboard hanya perlu skor, pertimbangkan memisahkan state detail ke tabel lain:

CREATE TABLE game_result_states (
  result_id BIGINT PRIMARY KEY,
  grid_state TEXT NOT NULL
);

Manfaatnya:

  • Tabel utama lebih ramping.
  • Index leaderboard tidak “tercemar” oleh row yang terlalu besar.
  • I/O untuk query ranking menurun karena data yang dibaca lebih kecil.

Trade-off-nya adalah perlu join saat Anda benar-benar ingin melihat detail state.

Strategi migrasi index aman tanpa downtime

Menambah atau mengganti index di produksi perlu hati-hati karena operasi ini dapat memakan waktu dan memengaruhi write. Pendekatan detail berbeda per engine, jadi gunakan fitur online/non-blocking index build yang tersedia pada database Anda bila memungkinkan.

Langkah migrasi yang aman

  1. Identifikasi query panas dari slow query log atau monitoring APM/database.
  2. Tambahkan index baru tanpa langsung menghapus yang lama.
  3. Uji dengan EXPLAIN pada query produksi yang relevan.
  4. Bandingkan metrik: latency p95/p99, rows examined, sort, dan load write.
  5. Roll out aplikasi bila query baru atau pagination baru memerlukan perubahan kode.
  6. Hapus index lama setelah yakin tidak dipakai lagi.

Pola migrasi praktis

Misalnya Anda ingin mengganti index leaderboard:

-- index lama kurang optimal
CREATE INDEX idx_results_seed ON game_results (daily_seed);

-- tambahkan index baru lebih dulu
CREATE INDEX idx_results_seed_rank
ON game_results (daily_seed, score DESC, attempts ASC, completed_at ASC, user_id);

Setelah itu:

  • jalankan EXPLAIN untuk memastikan planner mulai memilih index baru,
  • pantau dampak terhadap insert/update,
  • baru pertimbangkan menghapus index lama jika benar-benar redundan.

Jangan menjadwalkan pembuatan index besar di jam trafik puncak. Walau build online tersedia, operasi ini tetap memakai I/O, CPU, dan dapat memperpanjang latency query lain.

Hal yang perlu diawasi saat migrasi

  • Replikasi atau replica lag jika Anda memakai read replica.
  • Lonjakan disk usage karena index baru dibangun berdampingan dengan yang lama.
  • Query plan yang belum berubah karena statistik belum diperbarui.
  • Regresi write latency akibat terlalu banyak index tambahan.

Checklist audit performa untuk PostgreSQL/MySQL secara umum

  • Apakah query leaderboard memiliki index dengan urutan filter lalu sort?
  • Apakah query histori per user memakai composite index (user_id, created_at, id) atau setara?
  • Apakah hasil terbaru masih memakai OFFSET besar padahal feed terus tumbuh?
  • Apakah grid_state disimpan terpisah jika ukurannya besar dan jarang dibutuhkan?
  • Apakah ada query MAX()/GROUP BY berat yang seharusnya dipindahkan ke tabel ringkasan?
  • Apakah ada banyak index mirip yang menambah biaya write tanpa manfaat jelas?
  • Apakah EXPLAIN menunjukkan sort tambahan yang seharusnya bisa dihindari?
  • Apakah statistik database cukup mutakhir untuk memilih plan yang benar?
  • Apakah urutan pagination stabil dengan tie-breaker seperti id?
  • Apakah migrasi index direncanakan dengan build online/non-blocking dan observabilitas yang cukup?

Penutup

Optimasi leaderboard harian dengan index SQL bukan sekadar menambah index sebanyak mungkin. Yang paling menentukan adalah kecocokan antara pola query, urutan kolom pada composite index, dan bentuk data yang sebenarnya tumbuh di produksi. Untuk game puzzle harian yang menyimpan skor, percobaan, seed, dan peta grid, fokuskan perbaikan pada empat jalur kritis: leaderboard, best score per user, riwayat permainan, dan pagination hasil terbaru.

Mulailah dari query yang paling sering dan paling lambat, buat satu perubahan index yang terukur, cek dengan EXPLAIN, lalu pantau dampaknya terhadap read dan write. Jika leaderboard masih mahal karena banyak retry per user, tabel ringkasan best result per seed biasanya memberi hasil yang lebih stabil daripada terus memaksa agregasi di tabel mentah.