Postingan ini mau bahas tentang Query Optimization. Apa itu? Ya Optimasi Query. Artinya? Kalo dijelasin sekarang mungkin lu bakal bingung sama maksudnya. Nanti aja yah.
Untuk apa sebuah query dioptimasi? Apa kegunaannya? Supaya Query dapat dieksekusi dengan waktu yang cepat.
Kan udah pake core i7 kakak.. kok perlu dicepetin query-nya? Bukannya udah pasti cepet ya? Ah tidak juga. Mari kita lihat.

Misal kita dihadapkan dengan kasus database yang menyimpan data-data penjualan pulsa. Tetapi kita tidak akan membahas bagaimana data/tabel transaksinya. Kita disini akan membahas tabel parameter atau biasa dikenal dengan master data. Terdapat 3 buah tabel, yaitu OPERATOR (menyimpan data-data operator), PRODUCT (menyimpan data product/pulsa yang dijual), dan PHONE_PREFIX (menyimpan data prefix nomor telepon). Buat yang tidak tahu apa itu prefix telepon, prefix telepon adalah digit awal dari suatu nomor telepon. Misal IM3 memiliki prefix 0856, Simpati memiliki prefix 0812, dll.

Nah berikut ini tabel-tabel tersebut. (Data yang disajikan hanyalah data dummy yang disesuaikan dgn kondisi sebenarnya).

Tabel OPERATOR:

OPT_ID OPT_DESC
TLK TELKOMSEL
IND INDOSAT
XLA XL Axiata

Tabel PRODUCT:

PRODUCT_ID PRODUCT_DESC OPT_ID
1 Simpati TLK
2 Kartu AS TLK
3 IM3 IND
4 Mentari IND
5 XL Bebas XLA
6 XL Xtra XLA

Table PHONE_PREFIX:

PRODUCT_ID PREFIX
1 0812
1 0813
2 0852
3 0856
3 0857
4 0815
4 0816
5 0817
5 0818
6 0819

Nah, misal kita diminta untuk menampilkan data prefix kartu as berapa saja? tambahkan informasi data operator beserta dengan product-nya! query-nya mau seperti apa?
Buat yang ga ngerti pertanyaannya, kita diminta untuk membuat query yang menampilkan informasi seperti tabel berikut:

OPT_ID PULSA PREFIX
TLK Kartu As 0852

Nah, seperti apakah query-nya?

Oke, jawaban dari orang awam yang baru belajar query, adalah sebagai berikut:

SELECT t1.OPT_ID, t2.PRODUCT_DESC as PULSA, t3.PREFIX FROM operator t1, product t2, phone_prefix t3 WHERE t1.OPT_ID=t2.OPT_ID and t2.PRODUCT_ID=t3.PRODUCT_ID and t2.PRODUCT_ID =2

Well, jawaban di atas sudah benar. Tapi query di atas tidak optimum. Tidak Optimal! Lho, mengapa?
Coba kita hitung cost (biaya) dari query di-atas. Karena diatas menggunakan cross join, maka:

biaya = hasil kali jumlah row di masing-masing tabel
      = 3 * 6 * 10
      = 180 I/O (I/O = input output, merupakan satuan dari cost sebuah query)

Kakak, apa itu cross join? Nanti aja, kalu mau tahu skr, google aja.

Oke sekarang.. kan cuma 180 tuh cost nya.. kalau misalnya 1 I/O butuh biaya 0,1 detik berarti cuma makan waktu 18 detik. (18 didapet dari 180 I/O dikali 0,1 detik).
Sekarang coba kalau kita pakai data real di lapangan. Apakah jumlah row di tabel prefix hanya 10? Apakah operator hanya ada 3? apakah jenis pulsa cuma ada 6?

Misal sekarang operator ada 10, jenis pulsa misal ada 15, prefix ada 150. Berarti costnya = 10 * 15 * 150 = 22500 I/O. Jika tadi 1 I/O butuh 0,1 detik maka akan mengabiskan 2250 detik atau 37 menit 30 detik (padahal niatnya cuma mau nampilin prefix kartu as saja loh). Suatu query baru kerasa kalau datanya bener-bener guede banget. Dan untuk ini, kita perlu optimasi query-nya.

Lalu bagaimana cara optimasi querynya? Pecah querynya! (emang gelas:mrgreen: )
Caranya:

1. pisahkan query buat ambil data Kartu As

SELECT * FROM PRODUCT WHERE product_id = 2

2. JOIN dengan tabel lain, kita join dengan tabel OPERATOR dulu

SELECT t1.opt_id, t2.product_id, t2.product_desc FROM OPERATOR t1, (SELECT * FROM PRODUCT WHERE product_id = 2) t2 WHERE t1.opt_id = t2.opt_id

3. JOIN hasil nomor 2, dengan tabel PHONE_PREFIX

SELECT t1.opt_id, t1.product_desc as pulsa, t2.prefix FROM (SELECT t1.opt_id, t2.product_id, t2.product_desc FROM OPERATOR t1, (SELECT * FROM product WHERE product_id = 2) t2 WHERE t1.opt_id = t2.opt_id) t1, PHONE_PREFIX t2 WHERE t1.product_id = t2.product_id

Jadi jawabannya ada di step ke-3.
Querynya jadi lebih panjang donk? Iya begitulah. Optimalnya query tidak dilihat dari panjang atau pendek sintaksnya, tapi besar kecil cost nya. Semakin kecil cost nya, maka semakin optimal query tsb.

Taunya kalo query diatas memiliki cost yang kecil gimana? Kita lihat step 1, query tsb menghasilkan 1 row data. Hasil query ini, digunakan sebagai subquery pada step 2.

Hasil dari step 2 ini, menghasilkan 1 row juga. Lalu digunakan pada step ke-3. Berarti cost dari query pada step 3 adalah 1 dikali jumlah row pada table PHONE_PREFIX, atau setara dengan jumlah row pada tabel PHONE_PREFIX. Jika PHONE_PREFIX tadi memiliki 150 macam prefix, dan 1 I/O memakan waktu 0,1 detik maka cost nya adalah 0,1 dikali 150 = 15 detik. (Yang semula 37 menit 30 detik, kini hanya menjadi 15 detik!)

Seperti itulah Query Optimization. Hitungan2 di atas hanyalah ilustrasi semata. Cost atau biaya I/O sendiri sebetulnya tidak pernah dihitung dalam satuan detik. Contoh diatas hanya untuk memudahkan pemahaman saja. Semoga bermanfaat