11 Best Practice Untuk MySQL di Tahun 2025

1. Bagaimana Index Composite Bekerja dalam JOIN?

Saat Anda melakukan JOIN antara dua tabel, MySQL menggunakan indeks untuk:

• Mencari data pada tabel kedua berdasarkan nilai dari tabel pertama (key lookup).
• Memfilter data sebelum melakukan proses JOIN.

Jika indeks composite mencakup kolom-kolom yang sering digunakan dalam kondisi JOIN (misalnya, di klausa ON atau WHERE), maka indeks tersebut tetap membantu MySQL mempercepat query.

2. Contoh Kasus JOIN

Tabel dan Query:

Misalkan Anda memiliki dua tabel:

CREATE TABLE users (
    id INT PRIMARY KEY,
    nama VARCHAR(50),
    created_at DATE
);

CREATE TABLE transaksi (
    id INT PRIMARY KEY,
    user_id INT,
    tanggal DATE,
    total DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Query yang sering digunakan:

SELECT 
    u.id, u.nama, t.total, t.tanggal
FROM 
    users u
JOIN 
    transaksi t
ON 
    u.id = t.user_id
WHERE 
    t.tanggal BETWEEN '2024-01-01' AND '2024-01-31';

Tanpa Index Composite:

• MySQL harus mencari setiap user_id di tabel transaksi dan memfilter data tanggal satu per satu.
• Jika hanya ada indeks tunggal di user_id atau tanggal, pencarian tidak akan secepat jika ada indeks yang mencakup keduanya.

Dengan Index Composite:

Jika Anda membuat indeks composite pada transaksi(user_id, tanggal):

CREATE INDEX idx_user_tanggal ON transaksi (user_id, tanggal);

• MySQL dapat langsung mencari semua transaksi dengan user_id tertentu, yang jatuh dalam rentang tanggal yang diminta.
• Hasilnya lebih cepat, karena MySQL tidak harus memeriksa baris satu per satu.

3. Bagaimana Jika JOIN Menggunakan Kolom Lain?

Jika JOIN melibatkan kolom lain yang tidak termasuk dalam indeks composite, indeks tersebut masih dapat membantu dalam kombinasi dengan indeks lain, tergantung pada bagaimana query dirancang.

Contoh 1: Kolom JOIN Tidak Masuk Indeks

Jika Anda memfilter pada kolom yang berbeda, seperti total:

SELECT 
    u.id, u.nama, t.total, t.tanggal
FROM 
    users u
JOIN 
    transaksi t
ON 
    u.id = t.user_id
WHERE 
    t.total > 100 AND t.tanggal BETWEEN '2024-01-01' AND '2024-01-31';

• Indeks composite di (user_id, tanggal) tetap membantu untuk memfilter tanggal, meskipun total tidak termasuk dalam indeks.
• Namun, untuk memfilter total, MySQL perlu memindai baris tambahan yang memenuhi kondisi lainnya.

Solusi:

• Anda bisa menambahkan indeks tambahan di kolom total jika sering digunakan dalam filter.
• Namun, hindari membuat terlalu banyak indeks, karena bisa memperlambat operasi tulis (INSERT/UPDATE/DELETE).

4. Tips Optimasi JOIN dengan Indeks

1. Gunakan Indeks di Kolom yang Dicocokkan di JOIN:
   • Pastikan kedua tabel memiliki indeks pada kolom yang digunakan dalam klausa ON.
   • Contoh: users(id) dan transaksi(user_id).
2. Gunakan Index Composite Jika Ada Kondisi Tambahan:
   • Tambahkan kolom-kolom yang sering digunakan dalam kondisi filter atau pengurutan ke indeks composite.
   • Contoh: (user_id, tanggal) jika tanggal sering digunakan dalam filter WHERE.
3. Gunakan EXPLAIN untuk Menganalisis Query:
   • Lihat bagaimana MySQL menggunakan indeks dalam JOIN.
   • Perhatikan kolom key pada hasil EXPLAIN, yang menunjukkan indeks mana yang digunakan.
4. Cek Cardinality Kolom:
   • Indeks lebih efektif pada kolom dengan cardinality (keragaman nilai) tinggi.
   • Contoh: Kolom seperti user_id atau tanggal lebih cocok untuk indeks dibandingkan kolom is_deleted (biner).

5. Apakah Perlu Indeks Terpisah atau Composite untuk JOIN?

• Jika query JOIN selalu melibatkan kombinasi kolom, gunakan indeks composite.
• Jika kolom digunakan secara individual dalam query selain JOIN, pertimbangkan untuk membuat indeks terpisah.

Contoh Perbandingan:

• Composite Index: (user_id, tanggal)
  • Efisien untuk query yang memfilter pada kombinasi user_id dan tanggal.
• Indeks Terpisah: user_id dan tanggal (dua indeks berbeda)
  • Berguna jika user_id dan tanggal sering digunakan sendiri-sendiri dalam query.

6. Bagaimana MySQL Memproses JOIN?

• Dalam query JOIN, MySQL menentukan urutan eksekusi tabel (driving table) berdasarkan query Anda.
• Tabel pertama yang diproses disebut driving table, sedangkan tabel lainnya disebut driven table.
• MySQL akan menggunakan indeks untuk mencari data pada tabel kedua (driven table) berdasarkan tabel pertama (driving table).

Contoh:

SELECT *
FROM users u
JOIN transaksi t
ON u.id = t.user_id;

Di sini:

• users adalah driving table.
• transaksi adalah driven table.
• MySQL akan membaca data dari users terlebih dahulu, lalu mencocokkannya dengan data di transaksi menggunakan t.user_id.

7. Pengaruh Posisi Kolom di ON

Secara sintaks SQL:

ON u.id = t.user_id

dan

ON t.user_id = u.id

adalah identik. MySQL akan memahami bahwa Anda ingin mencocokkan u.id dengan t.user_id.

Namun, performa query tidak ditentukan oleh posisi kolom di ON, melainkan oleh:

• Urutan tabel dalam JOIN (tabel driving dan driven).
• Indeks pada kolom yang terlibat dalam kondisi JOIN.

8. Bagaimana Urutan Tabel Mempengaruhi Performa?

Contoh Kasus:

Misalkan Anda memiliki dua tabel:

users (1.000 baris)
transaksi (1.000.000 baris)

Query:

SELECT *
FROM users u
JOIN transaksi t
ON u.id = t.user_id;

Skema Eksekusi:

• Jika MySQL memulai dengan users (driving table), maka hanya 1.000 baris yang akan diproses lebih dahulu, dan MySQL akan mencari cocokannya di transaksi (1.000.000 baris).
• Jika MySQL memulai dengan transaksi, maka 1.000.000 baris akan diproses lebih dulu, lalu mencocokkannya dengan 1.000 baris di users.

Optimasi:

• Pilih tabel yang lebih kecil sebagai driving table untuk meminimalkan jumlah pencarian di tabel kedua.
• Pastikan kolom yang digunakan dalam ON memiliki indeks di tabel driven.

9. Urutan Tabel di Query JOIN

Urutan tabel dalam query JOIN memengaruhi hasil perintah EXPLAIN, yang menunjukkan bagaimana MySQL memilih driving table.

Contoh 1: Tabel kecil sebagai driving table

SELECT *
FROM users u
JOIN transaksi t
ON u.id = t.user_id;

Contoh 2: Tabel besar sebagai driving table

SELECT *
FROM transaksi t
JOIN users u
ON t.user_id = u.id;

Perbedaan:

• Query 1 lebih efisien jika users lebih kecil.
• Query 2 lebih efisien jika transaksi lebih kecil.

Catatan: MySQL biasanya memilih tabel yang lebih kecil sebagai driving table, kecuali ada petunjuk eksplisit seperti STRAIGHT_JOIN.

10. Gunakan EXPLAIN untuk Memahami Performa

Gunakan perintah EXPLAIN untuk melihat bagaimana MySQL mengeksekusi query Anda. Contoh:

EXPLAIN SELECT *
FROM users u
JOIN transaksi t
ON u.id = t.user_id;

Hal yang Dilihat:

• Kolom table: Urutan tabel yang diproses.
• Kolom key: Indeks yang digunakan pada tabel tersebut.
• Kolom rows: Perkiraan jumlah baris yang diproses.

11. Best Practices untuk JOIN

1. Indeks pada Kolom JOIN:
   • Pastikan kolom di klausa ON memiliki indeks.
   • Contoh: CREATE INDEX idx_user_id ON transaksi(user_id);
2. Pilih Urutan Tabel yang Tepat:
   • Tabel dengan jumlah baris lebih kecil biasanya lebih baik sebagai driving table.
3. Gunakan STRAIGHT_JOIN jika Diperlukan:
   • Jika MySQL salah memilih urutan tabel, gunakan STRAIGHT_JOIN untuk memaksa urutan.
   SELECT * FROM users u STRAIGHT_JOIN transaksi t ON u.id = t.user_id;
4. Gunakan Filter di Tabel Awal:
   • Pastikan kondisi filter (WHERE) diterapkan sedini mungkin untuk mempersempit data driving table.
   SELECT * FROM users u JOIN transaksi t ON u.id = t.user_id WHERE u.nama = 'John';