Bahasa SQL‎ > ‎

Membuat Index di MySQL

Pendahuluan

Index adalah objek pada MySQL yang berisi data yang terurut - dari nilai-nilai pada satu atau lebih field dalam suatu table. 

Sama seperti daftar isi pada sebuah buku, index terutama digunakan  untuk mempercepat pencarian terhadap suatu set data dengan kondisi tertentu - yang melibatkan kombinasi field yang sudah didefinisikan dalam suatu index. 

Tanpa index, pencarian data biasanya akan memakan waktu lama, terutama jika data sudah dalam skala jumlah yang sangat besar.

Syntax Pembuatan Index

Syntax pada MySQL untuk membuat suatu index adalah sebagai berikut :

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_type]
 
index_col_name:
    col_name [(length)] [ASC | DESC]
 
index_type:
    USING {BTREE | HASH}

Contoh Pembuatan Index dan Penggunaannya

Table "tr_penjualan" pada database "phi-minimart" memiliki sekitar 400 ribu baris data. Dan jika kita perhatikan struktur table tersebut, terdapat banyak foreign key yang jika dilakukan join ke master table akan cukup lambat.

Sebagai gambaran, coba lakukan eksekusi join antara table "tr_penjualan", "ms_produk" dan "ms_cabang" dengan perintah SQL berikut:

SELECT t1.*, m1.nama_produk, m2.nama_cabang FROM 
tr_penjualan t1
JOIN ms_produk m1 ON t1.kode_produk = m1.kode_produk
JOIN ms_cabang m2 ON t1.kode_cabang = m2.kode_cabang
LIMIT 0, 1000000;

dan perhatikan waktu eksekusi dan pengembalian hasilnya. Berikut adalah hasil catatan waktu yang saya dapat dalam 3 kali eksekusi dengan SQLYog :

Catatan Waktu Eksekusi Sebelum Index

Eksekusi 1 

427058 row(s) returned

Execution Time : 00:00:00:000
Transfer Time  : 00:00:08:547
Total Time     : 00:00:08:547

Eksekusi 2

427058 row(s) returned

Execution Time : 00:00:00:016
Transfer Time  : 00:00:08:547
Total Time     : 00:00:08:563

Eksekusi 3

427058 row(s) returned

Execution Time : 00:00:00:000
Transfer Time  : 00:00:08:469
Total Time     : 00:00:08:469

Sekarang mari kita lakukan index pada dua kolom join pada table "tr_penjualan" tersebut, yaitu "kode_produk" dan "kode_cabang" denga perintah SQL berikut ini :

CREATE INDEX nama_idx_tr_penj1
ON tr_penjualan(kode_produk, kode_cabang) USING BTREE;

Tunggu beberapa saat sampai index tersebut selesai dibuat.


Sekarang eksekusi kembali perintah "Select..." di atas dan lihat hasil catatan waktunya. Berikut adalah catatan waktu yang saya dapatkan untuk 3 kali eksekusi.

Catatan Waktu Eksekusi Setelah Index

Eksekusi 1

427058 row(s) returned

Execution Time : 00:00:00:016
Transfer Time  : 00:00:08:297
Total Time     : 00:00:08:313

Eksekusi 2

427058 row(s) returned

Execution Time : 00:00:00:000
Transfer Time  : 00:00:08:062
Total Time     : 00:00:08:062

Eksekusi 3

427058 row(s) returned

Execution Time : 00:00:00:000
Transfer Time  : 00:00:07:766
Total Time     : 00:00:07:766


Terlihat lebih baik bukan performanya ?

Contoh Penggunaan Pada Perintah Update

Efek index pada perintah "Select.." pada contoh di atas hanya memberikan perbedaan beberapa mili second, mari kita lihat dampak penggunaan index pada perintah update.

Pertama kita hapus kembali index yang sudah kita buat di atas dengan perintah berikut ini :

DROP INDEX nama_idx_tr_penj1 ON tr_penjualan;

Setelah itu, coba lakukan perintah update berikut ini dimana kita akan memberikan nilai baru ke kolom "jumlah_pembelian"  untuk produk apel, kentang, nangka di semua cabang PHI.

UPDATE tr_penjualan t1, ms_produk m1, ms_cabang m2
SET t1.jumlah_pembelian = 2 * t1.jumlah_pembelian
WHERE
t1.kode_produk = m1.kode_produk AND
t1.kode_cabang = m2.kode_cabang AND
(
    m1.nama_produk = 'apel 1 kg' OR
    m1.nama_produk = 'kentang 1 kg' OR
    m1.nama_produk = 'nangka 1 kg'
) AND
m2.nama_cabang LIKE 'PHI%';

Catat waktu eksekusi dari perintah tersebut setelah selesai dilakukan. Berikut ini adalah 3 kali catatan waktu hasil eksekusi tanpa index.

Catatan Waktu Eksekusi Sebelum Index

Eksekusi 1

32360 row(s) affected

Execution Time : 00:00:09:079
Transfer Time  : 00:00:00:000
Total Time     : 00:00:09:079

Eksekusi 2

32360 row(s) affected

Execution Time : 00:00:09:062
Transfer Time  : 00:00:00:000
Total Time     : 00:00:09:062

Eksekusi 3

32360 row(s) affected

Execution Time : 00:00:08:297
Transfer Time  : 00:00:00:000
Total Time     : 00:00:08:297

Dan sekarang, coba buat lagi index untuk kedua kolom sama dengan perintah sebelumnya.

CREATE INDEX nama_idx_tr_penj1
ON tr_penjualan(kode_produk, kode_cabang) USING BTREE;

Tunggu beberapa saat sampai dengan selesai. Jika sudah, coba lakukan kembali perintah update di atas. Apakah catatan waktu menunjukkan hasil yang lebih baik ? 

Sebagai perbandingan kembali, berikut adalah 3 kali hasil pencatatan waktu eksekusi perintah update setelah dilakukan index. Terlihat hasil yang jauh lebih baik.

Catatan Waktu Eksekusi Setelah Index

Eksekusi 1

32360 row(s) affected

Execution Time : 00:00:08:109
Transfer Time  : 00:00:00:000
Total Time     : 00:00:08:109

Eksekusi 2

32360 row(s) affected

Execution Time : 00:00:07:218
Transfer Time  : 00:00:00:000
Total Time     : 00:00:07:218

Eksekusi 3

32360 row(s) affected

Execution Time : 00:00:06:891
Transfer Time  : 00:00:00:000
Total Time     : 00:00:06:891

Kesimpulan

Dengan contoh pembuatan index dan efektivitasnya pada kedua contoh dengan "Select" dan "Update" terlihat penggunaan index berperan signifikan dalam meningkatkan performa jika terjadi join, lookup atau pencarian data referensi. Dengan demikian, biasakan gunakan index terutama untuk foreign key yang digunakan untuk join ke table lain dari table Anda.

Semoga artikel ini bermanfaat.


Comments