Tips – tips optimasi query SQL dengan index, subquery, dan cross join

1. Gunakan tipe data numeric sebagai primary key or foreign key

Seringkali pemula akan mendesign tabel dengan menggunakan tipe string sebagai key, dan hal ini akan berakibat pada performance dikemudian hari apabila data sudah besar, sehingga berimplikasi pada perubahan tabel dan coding, dan hal ini kadang sulit dilakukan karena jika sistim sudah diimplementasikan database sudah terisi data-data.

contoh berikut table mahasiswa untuk nilai mata kuliah

tb_mhs 
id int auto increment primary key
nik varchar(50)
nama varchar(250)
dob datetime
gender varchat(1)

tb_matakuliah
id  int auto increment primary key
keterangan varchar(250)
sks int

tb_nilai
no  int auto increment primary key
id_mhs int
id_matakuliah int
nilai float

Perhatikan pada tabel tb_nilai yang merupakan relasi penghubung dari tabel tb_mhs dan tb_matakuliah, tipe data yang digunakan adalah integer pada kolom id_mhs dan id_matakuliah

2. Fungsi subquery untuk mempersingkat proses pengambilan data

Contoh

SELECT m.id, m.nik, m.nama, m.dob, m.gender
FROM tb_mhs m
JOIN tb_nilai n ON m.id = n.id_mhs
JOIN tb_matakuliah mk ON n.id_matakuliah = mk.id

Dengan menggunakan subquery kita bisa mempersingkat pengambilan data pada proses join dengan tb_nilai,
daripada menggunakan join dan melakukan scanning keseluruhan table pada tb_nilai, kita bisa menggunakan
subquery dengan meringkas penscaningan data

SELECT m.id, m.nik, m.nama, m.dob, m.gender
FROM tb_mhs m
JOIN (SELECT * FROM tb_nilai WHERE id_mhs = 1) n ON m.id = n.id_mhs
JOIN tb_matakuliah mk ON n.id_matakuliah = mk.id

Note: contoh query di atas dapat dilakukan dengan catatan kita hanya menfilter untuk satu atau beberapa mahasiswa saja.

3. Fungsi update dengan join

Terkadang kita ingin melakukan update dengan mengkosongkan nilai pada tabel tb_nilai dengan nama mahasiswa tertentu,
maka kita bisa menggunakan perintah update dengan penghubung where.

UPDATE tb_nilai n, tb_mhs m
SET n.nilai =0
WHERE n.id_mhs = m.id
AND m.id IN (1, 2, 3, 4)

4. Fungsi cross join yang jarang digunakan

Tabel tb_nilai merupakan tabel relasi penghubung antara tabel tb_mhs dan tb_matakuliah,
dan kita dapat mengenerate tabel tb_nilai dengan menggunakan perintah cross join

INSERT INTO tb_nilai (id_mhs, id_matakuliah)
SELECT m.id, mk.id
FROM tb_mhs m
CROSS JOIN tb_matakuliah mk

Pada aplikasi retail, cross join biasa digunakan penulis untuk mengenerate table stok yang merupakan
penghubung antara tabel produk dan tabel gudang

INSERT INTO tb_stok (id_produk, id_gudang)
SELECT m.id, g.id
FROM tb_produk m
CROSS JOIN tb_gudang g

5. Letakkan index pada posisi yang tepat

Seiring dengan berjalannya waktu, data akan terus bertambah, maka sangat perlu meletakkan index pada kolom yang
sering digunakan untuk pencarian atau key penghubung join.
Ingat index yg berlebihan akan memperlambat kinerja, gunakan index seperlunya.
Dengan contoh tabel di atas maka index perlu diletakkan pada tb_nilai untuk kolom id_mhs dan juga id_matakuliah.
Biasanya index otomatis akan dicreate untuk kolom dengan atribut primary key.