Mengenal MySQL Index

Mengenal MySQL Index Untuk Mempercepat Proses Query

Biasanya orang yang baru mulai belajar programming tidak mengetahui bahwa untuk proses query ke mysql ada yang namanya Index atau kadang-kadang juga disebut Indexing. Pada dasarnya Index dibuat adalah untuk mempercepat proses query, terutama untuk website-website yang memiliki banyak pengunjung yang perlu secepatnya memberikan response ke usernya.

Indexing sendiri bekerja pada kolom / field, bukan tabel secara keseluruhan, jadi ketika membuat index, kita harus tahu kolom mana yang digunakan untuk kondisi WHERE dalam query Anda. Indexing bisa untuk kolom tunggal (single) atau bisa juga untuk banyak kolom (multiple).

Kelebihan dari menggunakan Index adalah proses query Menjadi sangat cepat, namun selain kelebihan ada juga kekurangan, yaitu proses perubahan data (Insert, Update, Delete) menjadi sedikit lebih lambat, karena setiap kali ada perubahan data, MySQL akan melakukan proses Indexing ulang. Semakin banyak index, maka proses perubahan akan semakin lama, terutama untuk Multiple Indexing. Kecuali sangat dibutuhkan, sebaiknya multiple indexing dihindari, kecuali jika tabel jarang ada perubahan data.

Multiple Indexing berbeda dengan Multiple Single Indexing, artinya dalam satu tabel, ada beberapa kolom yang dibikin index, tapi secara terpisah, misalnya index1, index2 tapi masing-masing 1 kolom, itu adalah Multiple Single Indexing. Sedangkan contoh Multiple Indexing adalah index1 dibikin index atas kolom1 dan kolom2, index2 dibikin index atas kolom3 dan kolom4.

Contoh Multiple Single Indexing adalah sebagai berikut :

CREATE TABLE `contoh_multiple_single_index` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data1` varchar(255) DEFAULT NULL,
  `data2` varchar(255) DEFAULT NULL,
  `data3` varchar(255) DEFAULT NULL,
  `data4` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index1` (`data1`),
  KEY `index2` (`data2`),
  KEY `index3` (`data3`),
  KEY `index4` (`data4`)
) ENGINE=MyISAM ;

Sedangkan Contoh Multiple Indexing adalah sebagai berikut :

CREATE TABLE `contoh_multile_index` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data1` varchar(255) DEFAULT NULL,
  `data2` varchar(255) DEFAULT NULL,
  `data3` varchar(255) DEFAULT NULL,
  `data4` varchar(255) DEFAULT NULL,
  `data5` varchar(255) DEFAULT NULL,
  `data6` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index1` (`data1`, `data2`),
  KEY `index2` (`data3`, `data4`, `data5`, `data6`)
) ENGINE=MyISAM ;

Sekarang kita akan melakukan test kecepatan query dengan menggunakan contoh koding PHP. Pertama yang harus Anda lakukan buatlah sebuah database dengan nama "belajar" dan tabel "belajar_mysql_index" dengan query create table berikut ini :

CREATE DATABASE `belajar` ;
USE `belajar` ;
CREATE TABLE `belajar_mysql_index` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data1` varchar(255) DEFAULT NULL,
  `data2` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index1` (`data1`)
) ENGINE=MyISAM ;

Di sini kita membuat tabel "belajar_mysql_index" di mana kolom data1 dan kolom data2 akan diisikan dengan data yang sama persis, namun hanya kolom satu saja yang diberi index.

Kedua, Anda gunakan file insert.php berikut ini untuk memasukan data ke dalam tabel "belajar_mysql_index", silakan salin koding berikut ini dan buatlah sebuah file dengan nama "insert.php" :

<?php
    $dbLink = mysqli_connect('localhost', 'root', '', 'belajar', 3306) ;
    if ($dbLink) {
        $dataSample = array('satu', 'dua', 'tiga', 'empat', 'lima', 'enam', 'tujuh', 'delapan', 'sembilan', 'sepuluh') ;
        $counterSuccess = 0 ;
        $counterFailed = 0 ;
        for ($i = 1 ; $i <= 100000 ; $i++) {
            $dataToInsert = mysqli_real_escape_string($dbLink, $dataSample[rand(0,count($dataSample) - 1)]) ;
            $queryInsert = sprintf("INSERT INTO `belajar_mysql_index` (`id`, `data1`, `data2`) VALUES (null, '%s', '%s') ;", $dataToInsert, $dataToInsert) ;
            if (mysqli_query($dbLink, $queryInsert)) {
                $counterSuccess++ ;
            } else {
                $counterFailed++ ;
            }
        }
        $counterTable = 0 ;
        $queryCount = "SELECT count(*) as `Jumlah` FROM `belajar_mysql_index` ;" ;
        if ($result = mysqli_query($dbLink, $queryCount)) {
            if ($row = mysqli_fetch_assoc($result)) {
                $counterTable = $row['Jumlah'] ;
            }
            mysqli_free_result($result) ;
        }
        mysqli_close($dbLink);
        print "Sukses Insert : " . $counterSuccess . "\n" ;
        print "Gagal  Insert : " . $counterFailed . "\n" ;
        print "Jumlah Data   : " . $counterTable . "\n" ;
    } else {
        print "Gagal Koneksi Ke Database\n" ;
    }
?>

Kemudian jalankan file php tersebut melalui CLI atau bisa juga lewat web browser. kurang lebih hasilnya akan seperti ini :

Di sini kita memasukkan 100.000 data ke dalam tabel "belajar_mysql_index", semakin banyak data, semakin kelihatan beda kecepatan antara Index dan Non-Index, jadi silakan merubah angka 100000 tersebut jika Anda mau melihat perbedaan yang semakin signifikan.

Setelah itu buatlah sebuah file php baru dengan nama "compare.php", silakan salin koding berikut ini dan buatlah file tersebut :

<?php
    $dbLink = mysqli_connect('localhost', 'root', '', 'belajar', 3306) ;
    if ($dbLink) {
        $counterData1 = 0 ;
        $counterData2 = 0 ;
        $timer = microtime(true) ;
        $queryCount = "SELECT count(*) as `Jumlah` FROM `belajar_mysql_index` WHERE `data1` = 'satu' ;" ;
        if ($result = mysqli_query($dbLink, $queryCount)) {
            if ($row = mysqli_fetch_assoc($result)) {
                $counterData1 = $row['Jumlah'] ;
            }
            mysqli_free_result($result) ;
        }
        $timerData1 = sprintf("%0.4f detik",microtime(true)-$timer) ;
        $timer = microtime(true) ;
        $queryCount = "SELECT count(*) as `Jumlah` FROM `belajar_mysql_index` WHERE `data2` = 'satu' ;" ;
        if ($result = mysqli_query($dbLink, $queryCount)) {
            if ($row = mysqli_fetch_assoc($result)) {
                $counterData2 = $row['Jumlah'] ;
            }
            mysqli_free_result($result) ;
        }
        $timerData2 = sprintf("%0.4f detik",microtime(true)-$timer) ;
        mysqli_close($dbLink);
        print "Query 1 Dengan Index : $counterData1 Dalam Waktu $timerData1\n" ;
        print "Query 2 Tanpa  Index : $counterData2 Dalam Waktu $timerData2\n" ;
    } else {
        print "Gagal Koneksi Ke Database\n" ;
    }
?>

Jika file compare.php ini dijalankan melalui CLI atau bisa juga melalui web browser, kira-kira hasilnya adalah sebagai berikut :

Di sini bisa dilihat bahwa menggunakan MySQL Index akan sangat jauh mempercepat proses query table.

Tips : untuk mengetahui apakah query Anda sudah benar-benar menggunakan index yang Anda buat, gunakan perintah "explain" di depan query yang Anda buat, maka nanti akan terlihat apakah sudah digunakan atau belum index tersebut.

Semoga informasi ini membantu Anda jika ketemu masalah kecepatan query di MySQL. Jika Anda punya pertanyaan seputar MySQL Index, silakan ke bagian tanya jawab dan isilah dibagian komentar, siapa tahu saya bisa membantu Anda.

File contoh bisa di download di sini