Index SQL untuk Tabel Redeem yang Makin Besar tanpa Query Lambat

Pada alur redeem/claim kode seperti konteks di repo kui123456789/cdk-redeem-only-extension, bottleneck paling sering bukan di logika aplikasi, melainkan di query ke tabel redeem yang terus membesar. Gejalanya biasanya sederhana: cek status kode mulai lambat, validasi user terasa berat saat trafik naik, histori klaim admin timeout, dan endpoint daftar data makin mahal karena database melakukan scan terlalu banyak baris.

Solusinya bukan sekadar “tambah index sebanyak mungkin”. Index SQL untuk tabel redeem harus disusun sesuai pola akses data yang nyata: query apa yang paling sering dipakai, kolom mana yang dipakai di WHERE, JOIN, ORDER BY, dan apakah hasilnya sempit atau lebar. Artikel ini membahas cara menurunkan use case redeem menjadi desain query dan indexing yang praktis, lalu menunjukkan kapan perlu index tunggal, kapan index komposit, mengapa urutan kolom penting, dan kapan harus meninggalkan OFFSET besar.

Studi kasus: tabel redeem yang cepat membesar

Dalam sistem redeem kode, ada beberapa alur yang hampir selalu muncul:

Cek status kode: apakah kode valid, aktif, belum dipakai, belum kedaluwarsa.
Validasi user: apakah user ini boleh klaim, sudah pernah klaim, atau melanggar limit.
Histori klaim: daftar klaim per user, per kode, atau rentang waktu tertentu.
Daftar admin: pencarian, filter status, sorting berdasarkan waktu terbaru.

Masalahnya, tabel redeem dan tabel klaim biasanya bertumbuh sangat berbeda. Tabel master kode mungkin tumbuh stabil, tetapi tabel histori klaim bisa naik sangat cepat. Jika query harian masih mengandalkan pencarian tanpa index yang cocok, database akan berulang kali membaca banyak baris yang sebenarnya tidak dibutuhkan.

Skema generik yang umum dipakai bisa terlihat seperti ini:

CREATE TABLE redeem_codes (
    id BIGINT PRIMARY KEY,
    code VARCHAR(64) NOT NULL,
    campaign_id BIGINT NULL,
    status VARCHAR(20) NOT NULL,
    max_claims INT NOT NULL DEFAULT 1,
    claimed_count INT NOT NULL DEFAULT 0,
    starts_at TIMESTAMP NULL,
    expires_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

CREATE TABLE redeem_claims (
    id BIGINT PRIMARY KEY,
    redeem_code_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    claim_status VARCHAR(20) NOT NULL,
    claimed_at TIMESTAMP NOT NULL,
    source VARCHAR(30) NULL,
    created_at TIMESTAMP NOT NULL,
    FOREIGN KEY (redeem_code_id) REFERENCES redeem_codes(id)
);

Di level aplikasi, query yang sering muncul kurang lebih seperti ini:

-- cek kode berdasarkan nilai code
SELECT id, status, starts_at, expires_at, max_claims, claimed_count
FROM redeem_codes
WHERE code = ?;

-- cek apakah user sudah pernah klaim kode tertentu
SELECT id
FROM redeem_claims
WHERE redeem_code_id = ? AND user_id = ?
LIMIT 1;

-- histori klaim per user, urut terbaru
SELECT id, redeem_code_id, claim_status, claimed_at
FROM redeem_claims
WHERE user_id = ?
ORDER BY claimed_at DESC
LIMIT 20;

-- daftar admin dengan filter status dan urut waktu buat
SELECT id, code, status, created_at
FROM redeem_codes
WHERE status = ?
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

Kalau index tidak mengikuti pola di atas, query akan pelan meskipun server aplikasi dan cache terlihat baik-baik saja.

Gejala bottleneck SQL pada alur redeem

Beberapa gejala yang layak dicurigai sebagai masalah index atau desain query:

Query sederhana berbasis kode atau user makin lambat seiring ukuran tabel naik.
CPU database tinggi walaupun jumlah request tidak melonjak drastis.
Endpoint admin dengan filter dan pagination lambat pada halaman besar.
Query yang sama kadang cepat, kadang lambat, tergantung parameter.
Waktu respon naik tajam saat fitur histori klaim atau export dipakai.

Anti-pattern yang sering terjadi pada use case redeem:

Menyimpan code tanpa index unik atau tanpa index sama sekali.
Mengandalkan index tunggal di tiap kolom, padahal query dominan butuh kombinasi kolom.
Memakai SELECT * di endpoint yang sering dipanggil.
Memakai OFFSET sangat besar untuk daftar admin atau histori lama.
Menambahkan terlalu banyak index tanpa mengukur dampaknya ke operasi tulis.

Setiap index mempercepat sebagian query baca, tetapi juga menambah biaya INSERT, UPDATE, dan penggunaan storage. Pada sistem redeem dengan trafik klaim tinggi, ini adalah trade-off nyata.

Membaca EXPLAIN: cari scan yang tidak perlu

Langkah pertama sebelum menambah index adalah melihat rencana eksekusi query dengan EXPLAIN. Sintaks detailnya berbeda antar mesin database, tetapi tujuannya sama: melihat apakah database memakai index yang tepat atau malah membaca terlalu banyak baris.

Contoh:

EXPLAIN
SELECT id
FROM redeem_claims
WHERE redeem_code_id = ? AND user_id = ?
LIMIT 1;

Saat membaca hasil EXPLAIN, fokus pada hal-hal berikut:

Apakah index dipakai? Jika tidak, kemungkinan ada mismatch antara query dan index.
Berapa banyak baris yang diperkirakan dibaca? Semakin besar, semakin mahal.
Apakah ada sorting tambahan? Jika ORDER BY tidak didukung index, database bisa membuat sort terpisah.
Apakah ada filter yang baru diterapkan setelah scan? Artinya index tidak cukup selektif.

Contoh diagnosis umum:

Query WHERE code = ? melakukan full table scan: berarti kolom code belum punya index yang cocok.
Query WHERE status = ? ORDER BY created_at DESC lambat: index hanya di status mungkin belum cukup, karena sorting masih mahal.
Query WHERE user_id = ? ORDER BY claimed_at DESC membaca banyak baris lalu sort: butuh index komposit yang mengikuti filter dan urutan.

EXPLAIN tidak menggantikan pengukuran nyata, tetapi sangat membantu menghindari menambah index secara membabi buta.

Memilih index tunggal vs index komposit

Index tunggal: untuk lookup sangat spesifik

Index tunggal cocok bila query dominan memang hanya memfilter satu kolom yang selektif.

CREATE UNIQUE INDEX idx_redeem_codes_code
ON redeem_codes (code);

Ini hampir selalu layak untuk sistem redeem karena pencarian berdasarkan kode adalah operasi inti. Jika setiap kode memang unik, gunakan index unik agar integritas data dan performa sama-sama terjaga.

Contoh query yang terbantu:

SELECT id, status, starts_at, expires_at, max_claims, claimed_count
FROM redeem_codes
WHERE code = ?;

Index komposit: untuk filter gabungan dan sorting

Kalau query sering memadukan beberapa kolom, index komposit lebih tepat daripada berharap database menggabungkan beberapa index tunggal.

Contoh validasi apakah user sudah pernah klaim kode tertentu:

CREATE INDEX idx_redeem_claims_code_user
ON redeem_claims (redeem_code_id, user_id);

Ini cocok untuk query:

SELECT id
FROM redeem_claims
WHERE redeem_code_id = ? AND user_id = ?
LIMIT 1;

Contoh histori klaim user, urut terbaru:

CREATE INDEX idx_redeem_claims_user_claimed_at
ON redeem_claims (user_id, claimed_at);

Ini membantu query:

SELECT id, redeem_code_id, claim_status, claimed_at
FROM redeem_claims
WHERE user_id = ?
ORDER BY claimed_at DESC
LIMIT 20;

Pada banyak mesin database, arah DESC di index punya perilaku spesifik. Jika Anda tidak yakin detail mesin yang dipakai, prinsip amannya adalah memastikan kolom filter dan kolom urut berada dalam index yang sesuai, lalu verifikasi dengan EXPLAIN.

Urutan kolom index menentukan hasil

Kesalahan yang sangat umum adalah memakai kolom yang benar, tetapi urutannya salah.

Bandingkan dua index berikut:

-- Opsi A
CREATE INDEX idx_claims_user_code
ON redeem_claims (user_id, redeem_code_id);

-- Opsi B
CREATE INDEX idx_claims_code_user
ON redeem_claims (redeem_code_id, user_id);

Keduanya tidak setara. Pilihannya harus mengikuti query dominan:

Jika query paling sering adalah WHERE redeem_code_id = ? AND user_id = ?, maka urutan (redeem_code_id, user_id) lebih masuk akal.
Jika query paling sering adalah melihat semua klaim milik user lalu difilter lanjutan, (user_id, redeem_code_id) bisa lebih cocok.

Aturan praktis yang aman:

Letakkan kolom yang paling sering dipakai sebagai filter awal di depan.
Setelah itu, tambahkan kolom yang sering dipakai bersama dalam query yang sama.
Jika ada ORDER BY, pertimbangkan kolom urut setelah kolom filter yang relevan.
Jangan membuat index komposit terlalu panjang tanpa alasan, karena biaya tulis dan storage ikut naik.

Untuk daftar admin, query seperti ini cukup umum:

SELECT id, code, status, created_at
FROM redeem_codes
WHERE status = ?
ORDER BY created_at DESC
LIMIT 50;

Index yang biasanya lebih cocok daripada index tunggal di status saja:

CREATE INDEX idx_redeem_codes_status_created_at
ON redeem_codes (status, created_at);

Mengapa? Karena database bisa lebih mudah menyaring berdasarkan status lalu mengambil urutan waktu tanpa sort besar di luar index.

Dampak SELECT * pada tabel redeem besar

SELECT * terlihat praktis, tetapi pada tabel yang membesar itu sering memperburuk performa:

Lebih banyak data dibaca dari disk atau buffer.
Lebih banyak data dikirim dari database ke aplikasi.
Index yang sebenarnya cukup untuk query tidak bisa dimanfaatkan optimal jika database tetap harus mengambil banyak kolom tambahan.

Contoh anti-pattern:

SELECT *
FROM redeem_claims
WHERE user_id = ?
ORDER BY claimed_at DESC
LIMIT 20;

Lebih baik pilih hanya kolom yang dibutuhkan:

SELECT id, redeem_code_id, claim_status, claimed_at
FROM redeem_claims
WHERE user_id = ?
ORDER BY claimed_at DESC
LIMIT 20;

Keuntungannya bukan hanya bandwidth. Pada beberapa kasus, query menjadi jauh lebih efisien karena database tidak perlu mengambil seluruh payload baris untuk kolom-kolom besar yang sebenarnya tidak dipakai.

Masalah OFFSET besar dan kapan pindah ke keyset pagination

OFFSET nyaman dipakai untuk halaman awal, tetapi menjadi mahal ketika angkanya besar. Query seperti ini:

SELECT id, code, status, created_at
FROM redeem_codes
WHERE status = ?
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

tetap memaksa database melewati ribuan baris sebelum menemukan 50 baris yang diminta. Pada daftar admin atau histori klaim yang makin dalam, biaya ini akan terasa.

Kapan OFFSET masih masuk akal

Dataset belum terlalu besar.
Halaman yang diakses umumnya halaman awal.
Pengalaman pengguna memang butuh lompat ke nomor halaman tertentu.

Kapan beralih ke keyset pagination

Jika pengguna lebih sering menelusuri data secara berurutan dari yang terbaru ke yang lama, keyset pagination lebih efisien.

Contoh:

-- halaman pertama
SELECT id, code, status, created_at
FROM redeem_codes
WHERE status = ?
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- halaman berikutnya, gunakan nilai baris terakhir halaman sebelumnya
SELECT id, code, status, created_at
FROM redeem_codes
WHERE status = ?
  AND (created_at < ? OR (created_at = ? AND id < ?))
ORDER BY created_at DESC, id DESC
LIMIT 50;

Pendekatan ini biasanya lebih stabil untuk data besar karena database tidak harus menghitung dan membuang banyak baris di depan.

Agar efektif, siapkan index yang sesuai dengan pola filter dan urut tersebut, misalnya kombinasi (status, created_at, id) bila query admin memang dominan seperti itu.

Contoh desain index untuk use case redeem

Berikut contoh paket index yang masuk akal untuk skema generik di atas. Ini bukan aturan mutlak; sesuaikan dengan query aktual aplikasi Anda.

-- lookup kode redeem
CREATE UNIQUE INDEX idx_redeem_codes_code
ON redeem_codes (code);

-- daftar admin/filter status + urut waktu
CREATE INDEX idx_redeem_codes_status_created_at
ON redeem_codes (status, created_at);

-- validasi apakah user pernah klaim kode tertentu
CREATE INDEX idx_redeem_claims_code_user
ON redeem_claims (redeem_code_id, user_id);

-- histori klaim per user, urut terbaru
CREATE INDEX idx_redeem_claims_user_claimed_at
ON redeem_claims (user_id, claimed_at);

-- histori klaim per kode, urut terbaru
CREATE INDEX idx_redeem_claims_code_claimed_at
ON redeem_claims (redeem_code_id, claimed_at);

Yang perlu dihindari adalah menambah semua kemungkinan kombinasi kolom sekaligus. Lebih baik:

Inventaris query paling mahal dan paling sering.
Kelompokkan pola WHERE dan ORDER BY.
Tambah index untuk query yang benar-benar penting.
Uji kembali dengan EXPLAIN dan pengukuran waktu nyata.

Anti-pattern umum pada query redeem

1. Memfilter dengan fungsi pada kolom terindeks

Contoh buruk:

SELECT id
FROM redeem_codes
WHERE LOWER(code) = LOWER(?);

Fungsi pada kolom bisa membuat index sulit dipakai. Lebih baik normalisasi data saat simpan, misalnya semua kode disimpan dalam huruf besar atau huruf kecil secara konsisten.

2. Kondisi OR yang tidak terencana

SELECT *
FROM redeem_codes
WHERE code = ? OR status = ?;

Query seperti ini sering sulit dioptimalkan dengan satu index yang rapi. Pisahkan alur query jika memang kebutuhan bisnisnya berbeda.

3. Index banyak, tapi query tetap ambil baris terlalu lebar

Index bagus tidak akan banyak menolong jika endpoint selalu mengambil kolom besar yang tidak diperlukan. Audit payload query, bukan hanya struktur index.

4. Mengira index tunggal di tiap kolom pasti cukup

Misalnya ada index terpisah pada user_id dan claimed_at, tetapi query dominan adalah WHERE user_id = ? ORDER BY claimed_at DESC. Dalam kasus seperti ini, index komposit biasanya lebih tepat.

5. Lupa biaya write amplification

Pada trafik klaim tinggi, tiap INSERT ke tabel histori harus memperbarui semua index terkait. Jika terlalu banyak index, throughput tulis bisa turun.

Strategi migrasi index yang aman

Menambah index pada tabel besar perlu hati-hati karena bisa memakan waktu dan berpotensi mengganggu beban produksi. Strategi amannya:

Mulai dari observasi: ambil daftar query lambat dan frekuensinya.
Tambah satu per satu: hindari migrasi besar berisi banyak index baru sekaligus.
Uji di staging dengan volume data representatif: jangan hanya pakai data kecil.
Jadwalkan pada jam beban rendah jika mekanisme pembuatan index berpotensi mahal.
Verifikasi sesudah deploy: cek kembali EXPLAIN, latensi query, dan dampak ke operasi tulis.
Hapus index yang tidak terpakai jika terbukti tidak memberi manfaat.

Jika sistem Anda sangat sensitif terhadap lock atau beban migrasi, pelajari fitur pembuatan index yang lebih aman pada mesin database yang dipakai. Nama fitur dan perilakunya berbeda-beda, jadi jangan mengasumsikan semua database punya kemampuan yang sama.

Jangan menilai keberhasilan migrasi index hanya dari satu query yang jadi cepat. Pastikan juga throughput tulis, waktu klaim puncak, dan performa halaman admin tetap sehat.

Checklist audit performa saat trafik naik

Gunakan checklist ini ketika volume redeem dan claim mulai tumbuh:

Apakah kolom code sudah punya index unik?
Apakah query validasi user memakai index komposit yang sesuai, bukan hanya index tunggal acak?
Apakah query histori user atau kode sudah punya index yang mendukung WHERE dan ORDER BY sekaligus?
Apakah endpoint yang sering dipanggil masih memakai SELECT *?
Apakah daftar admin menggunakan OFFSET besar yang mulai mahal?
Jika pagination makin dalam, apakah sudah waktunya beralih ke keyset pagination?
Apakah ada query yang memanggil fungsi pada kolom terindeks?
Apakah jumlah index pada tabel histori terlalu banyak dibanding pola query yang benar-benar dipakai?
Apakah hasil EXPLAIN menunjukkan scan atau sort besar yang sebenarnya bisa dihindari?
Apakah sesudah menambah index, performa tulis masih aman pada jam sibuk?

Penutup

Pada use case redeem/claim kode, performa database jarang rusak karena satu query yang sangat rumit. Biasanya masalah datang dari query sederhana yang dipanggil sangat sering, tetapi tidak didukung index yang sesuai. Karena itu, Index SQL untuk tabel redeem harus dirancang dari pola akses nyata: lookup kode, validasi user, histori klaim, dan daftar admin.

Mulailah dari query yang paling penting, baca EXPLAIN, buat index yang mengikuti WHERE dan ORDER BY, hindari SELECT * di jalur panas, dan evaluasi kapan OFFSET besar harus diganti dengan keyset pagination. Dengan pendekatan itu, tabel redeem bisa terus tumbuh tanpa membuat query inti ikut melambat.

Quick Search

shopping_cart Keranjang