Cara Menggunakan Indexes di MySQL

Table of Contents

Untuk meningkatkan kinerja operasi SELECT atau mempercepat eksekusi query, Anda dapat menambahkan index pada satu atau lebih kolom yang sering digunakan dalam query.

Pada bagian ini akan dibahas beberapa jenis index pada MySQL beserta contoh implementasinya.

Saya mengasumsikan Anda sudah menginstal MySQL di server. Anda dapat menggunakan database milik sendiri atau mengunduh sample database dari repositori berikut: GitHub – datacharmer/test_db

Primary Index
#

Primary key adalah jenis index yang menyimpan actual row data bersamaan dengan key-nya. Pada engine InnoDB, data disimpan langsung pada leaf node dari B-Tree primary index (clustered index).

Contoh paling umum dari penggunaan primary key adalah ketika Anda membuat sebuah tabel dengan kolom seperti id, kemudian menambahkan atribut AUTO_INCREMENT dan menetapkannya sebagai PRIMARY KEY. Dengan konfigurasi ini, pengambilan data menggunakan klausa WHERE id = ... akan dieksekusi dengan sangat cepat.

Hal ini terjadi karena MySQL tidak perlu melakukan full table scan, melainkan langsung mengambil baris data berdasarkan nilai id yang dicari. Selain itu, penggunaan AUTO_INCREMENT membuat data tersimpan secara terurut, sehingga pencarian menjadi lebih efisien.

Sebagai contoh, saya menggunakan tabel employees yang berisi sekitar 300.024 baris data, dengan skema sebagai berikut:

mysql> DESC employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int           | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

Ketika menjalankan query dengan emp_no sebagai WHERE clause, MySQL dapat mengeksekusi query dengan cepat karena tidak perlu memindai seluruh 300 ribu baris data. MySQL cukup mengambil satu baris yang sesuai dengan nilai emp_no yang diminta.

Perhatikan hasil EXPLAIN berikut, khususnya pada kolom key dan rows:

mysql> EXPLAIN SELECT * FROM employees WHERE emp_no = 15000;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

Dari hasil di atas dapat dilihat bahwa:

Index yang digunakan adalah PRIMARY
Jumlah baris yang diakses (rows) hanya 1

Waktu yang dibutuhkan untuk menjalankan query tersebut sangat singkat, sekitar 0.0006 detik:

mysql> SELECT * FROM employees WHERE emp_no = 15000;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  15000 | 1959-11-29 | Thanasis   | Bahi      | F      | 1988-03-27 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)

Contoh ini menunjukkan bagaimana penggunaan Primary Index dapat secara signifikan meningkatkan performa query SELECT, terutama pada tabel dengan jumlah data yang besar.

Secondary Index
#

Berbeda dengan primary index, secondary index tidak menyimpan actual row data. Pada InnoDB, secondary index hanya menyimpan nilai kolom yang di-index dan primary key pada leaf node. Primary key tersebut kemudian digunakan sebagai locator untuk mengambil data lengkap dari clustered index (primary index).

Konsekuensinya, nilai primary key akan terduplikasi pada setiap secondary index. Oleh karena itu, penggunaan primary key bertipe VARCHAR atau bertipe data besar tidak disarankan, karena akan meningkatkan ukuran index secara signifikan dan berdampak pada performa serta penggunaan storage.

Secondary index umumnya digunakan ketika terdapat kebutuhan untuk menjalankan query secara berulang atau cukup sering dengan filter berdasarkan kolom tertentu selain primary key.

Contoh Implementasi
#

Contoh berikut masih menggunakan tabel employees dengan struktur sebagai berikut:

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int           | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

Misalnya, aplikasi sering menjalankan query dengan kolom hire_date sebagai filter:

mysql> SELECT * FROM employees WHERE hire_date = '1991-05-26';

Query tersebut mengembalikan sekitar 65 baris data, namun membutuhkan waktu cukup lama:

65 rows in set (0.30 sec)

Jika dilihat menggunakan EXPLAIN, MySQL harus melakukan full table scan untuk menemukan data yang sesuai:

mysql> EXPLAIN SELECT * FROM employees WHERE hire_date = '1991-05-26';
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299600 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+

Dari hasil di atas dapat dilihat bahwa:

Tidak ada index yang digunakan (key = NULL)
MySQL memindai hampir seluruh 300 ribu baris data

Menambahkan Secondary Index
#

Karena query ini sering digunakan, maka perlu ditambahkan secondary index pada kolom hire_date:

ALTER TABLE employees 
ADD INDEX idx_hire_date (hire_date);

Untuk melihat penggunaan ruang penyimpanan index, gunakan query berikut:

SELECT 
  database_name,
  table_name,
  index_name,
  ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_in_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size'
  AND index_name != 'PRIMARY'
  AND table_name = 'employees';

Hasilnya:

+---------------+------------+---------------+------------+
| database_name | table_name | index_name    | size_in_mb |
+---------------+------------+---------------+------------+
| employees     | employees  | idx_hire_date |       4.52 |
+---------------+------------+---------------+------------+

Artinya, secondary index idx_hire_date membutuhkan sekitar 4.52 MB ruang penyimpanan.

Dampak Setelah Index Ditambahkan
#

Setelah index ditambahkan, jalankan kembali EXPLAIN:

mysql> EXPLAIN SELECT * FROM employees WHERE hire_date = '1991-05-26';
+----+-------------+-----------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_hire_date | idx_hire_date | 3       | const |   65 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+---------------+---------+-------+------+----------+-------+

Perbedaannya cukup jelas:

Index yang digunakan adalah idx_hire_date
Jumlah baris yang di-scan (rows) hanya 65
Tidak lagi melakukan full table scan

Waktu eksekusi query juga meningkat secara signifikan menjadi sekitar 0.0008 detik:

65 rows in set (0.00 sec)

Kesimpulan
#

Secondary index sangat efektif untuk meningkatkan performa query yang sering menggunakan kolom non-primary key sebagai filter. Namun, perlu diperhatikan:

Secondary index menambah penggunaan storage
Primary key akan terduplikasi pada setiap secondary index
Terlalu banyak index dapat berdampak negatif pada operasi INSERT, UPDATE, dan DELETE

Composite Index
#

Composite index adalah jenis index yang memungkinkan penggunaan lebih dari satu kolom sebagai key (hingga maksimal 16 kolom). Jenis index ini juga sering disebut sebagai multi-column index atau compound index.

Pada composite index, key index dibentuk berdasarkan urutan kolom yang didefinisikan. Misalnya, index yang dibuat dari (c1, c2, c3) akan membentuk struktur key secara berurutan menjadi c1 → c2 → c3. Karena itu, penggunaan index ini harus mengikuti aturan leftmost prefix.

Artinya, pencarian hanya akan menggunakan index jika kondisi WHERE menyertakan kolom paling kiri terlebih dahulu.

Contoh penggunaan index (c1, c2):

WHERE c1 = 'a' → menggunakan index
WHERE c2 = 'b' → tidak menggunakan index
WHERE c1 = 'a' AND c2 = 'b' → menggunakan index

Contoh Implementasi
#

Contoh berikut masih menggunakan tabel employees dengan struktur sebagai berikut:

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int           | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

Misalnya, ingin menjalankan query dengan filter berdasarkan kolom last_name dan gender:

mysql> EXPLAIN 
SELECT * 
FROM employees 
WHERE last_name = 'Aamodt' 
  AND gender = 'F';

Hasil EXPLAIN menunjukkan bahwa MySQL masih melakukan full table scan:

| type | key  | rows   | Extra       |
| ALL  | NULL | 299202 | Using where |

Hal ini menandakan bahwa MySQL harus memindai seluruh baris data untuk menemukan hasil yang sesuai.

Menambahkan Composite Index
#

Untuk mempercepat query tersebut, buat composite index pada kolom last_name dan gender:

ALTER TABLE employees 
ADD INDEX idx_lastname_gender (last_name, gender);

Setelah index ditambahkan, jalankan kembali query yang sama:

mysql> EXPLAIN 
SELECT * 
FROM employees 
WHERE last_name = 'Aamodt' 
  AND gender = 'F';

Hasilnya:

| type | key                 | rows | Extra                 |
| ref  | idx_lastname_gender |   85 | Using index condition |

Jumlah baris yang di-scan menjadi jauh lebih sedikit, menandakan bahwa index bekerja dengan baik dan memperkecil scope data yang perlu diproses oleh MySQL.

Pengaruh Urutan Kolom (Leftmost Prefix)
#

Sesuai dengan cara kerja composite index, jika hanya menggunakan kolom last_name sebagai filter, index masih dapat digunakan karena last_name berada di posisi paling kiri:

mysql> EXPLAIN 
SELECT * 
FROM employees 
WHERE last_name = 'Aamodt';

| type | key                 | rows |
| ref  | idx_lastname_gender |  205 |

Namun, jika hanya menggunakan kolom gender sebagai filter, index tidak akan digunakan, karena tidak memenuhi aturan leftmost prefix:

mysql> EXPLAIN 
SELECT * 
FROM employees 
WHERE gender = 'F';

| type | key  | rows   | Extra       |
| ALL  | NULL | 299202 | Using where |

Pada kondisi ini, MySQL kembali melakukan full table scan.

Kesimpulan
#

Composite index sangat efektif untuk query yang sering memfilter lebih dari satu kolom
Urutan kolom dalam index sangat menentukan apakah index dapat digunakan
Selalu letakkan kolom dengan kardinalitas tinggi atau yang paling sering digunakan di awal index
Salah urutan kolom dapat membuat index tidak terpakai

UNIQUE Key Index
#

Unique key index adalah jenis index yang berfungsi untuk menjaga integritas data pada sebuah tabel dengan memastikan tidak ada nilai yang duplikat pada kolom tertentu.

Berbeda dengan index biasa yang hanya bertujuan meningkatkan performa query, unique key index juga menambahkan constraint pada level database. Artinya, MySQL akan menolak operasi INSERT atau UPDATE yang menghasilkan nilai duplikat pada kolom yang memiliki unique constraint.

Secara default, ketika sebuah kolom ditetapkan sebagai PRIMARY KEY, MySQL otomatis menerapkan sifat UNIQUE. Namun, unique key juga dapat ditambahkan pada kolom non-primary key, sesuai dengan kebutuhan bisnis.

Contoh Implementasi
#

Sebagai contoh, dibuat sebuah tabel user dengan struktur berikut:

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| user  | varchar(64)  | NO   |     | NULL    |                |
| name  | varchar(128) | NO   |     | NULL    |                |
| email | varchar(128) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

Misalnya, Anda ingin memastikan bahwa setiap user tidak dapat mendaftar lebih dari satu kali dengan alamat email yang sama. Untuk itu, tambahkan unique key pada kolom email:

ALTER TABLE `user` 
ADD UNIQUE uniq_email (email);

Pengujian Unique Key
#

Kemudian, lakukan pengujian dengan mencoba memasukkan data menggunakan email yang sama lebih dari satu kali:

mysql> INSERT INTO `user` VALUES (NULL, 'jonh', 'jonh', 'jonh@mailme.com');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `user` VALUES (NULL, 'jonh', 'jonh', 'jonh@mailme.com');
ERROR 1062 (23000): Duplicate entry 'jonh@mailme.com' for key 'user.uniq_email'

mysql> INSERT INTO `user` VALUES (NULL, 'jonh', 'jonh', 'jonh@mailme.com');
ERROR 1062 (23000): Duplicate entry 'jonh@mailme.com' for key 'user.uniq_email'

Dari hasil di atas dapat disimpulkan bahwa:

MySQL berhasil menyimpan data pada insert pertama
Insert berikutnya gagal karena melanggar unique constraint
Database secara otomatis menjaga konsistensi dan keunikan data

Catatan Penting
#

Unique key tetap merupakan index, sehingga dapat digunakan untuk mempercepat query SELECT
Unique key tidak mengizinkan nilai duplikat, namun mengizinkan NULL (tergantung DBMS dan konfigurasi)
Unique key sangat cocok digunakan untuk kolom seperti:
- email
- username
- phone_number
- identity_number

Kesimpulan
#

UNIQUE key index merupakan kombinasi antara constraint dan index yang sangat penting untuk menjaga kualitas dan keandalan data. Dengan menerapkan unique key di level database, Anda dapat mencegah duplikasi data tanpa harus bergantung sepenuhnya pada validasi di sisi aplikasi.

Best Practices
#

1. Hindari Penggunaan `OR`, Gunakan `UNION`
#

Penggunaan operator OR sering kali membuat optimizer tidak dapat memanfaatkan index secara optimal, terutama jika kondisi melibatkan kolom yang berbeda.

-- Kurang optimal (menggunakan OR)
SELECT * FROM users 
WHERE country = 'Indonesia' OR country = 'Malaysia';

-- Lebih optimal (menggunakan UNION, index bisa digunakan)
SELECT * FROM users WHERE country = 'Indonesia'
UNION
SELECT * FROM users WHERE country = 'Malaysia';

Untuk kondisi pada kolom yang berbeda:

-- Kurang optimal (OR)
SELECT * FROM users WHERE id = 10 OR email = 'test@example.com';

-- Lebih optimal (UNION)
SELECT * FROM users WHERE id = 10
UNION
SELECT * FROM users WHERE email = 'test@example.com';

MySQL versi baru kadang mampu mengoptimasi OR, namun UNION tetap lebih konsisten untuk query kritikal.

2. Pastikan Tipe Data Sesuai dengan Index
#

Perbandingan tipe data yang tidak sesuai dapat menyebabkan implicit conversion, sehingga index tidak digunakan.

-- Kurang optimal (id bertipe INT dibandingkan dengan string)
SELECT * FROM orders WHERE id = '100';

-- Lebih optimal
SELECT * FROM orders WHERE id = 100;

3. Hindari Ekspresi atau Fungsi pada Kolom yang Di-Index
#

Menggunakan fungsi pada kolom yang di-index membuat MySQL tidak dapat menggunakan index tersebut.

-- Kurang optimal
SELECT * FROM customers WHERE YEAR(created_at) = 2024;

-- Lebih optimal (gunakan range)
SELECT * FROM customers
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

4. Gunakan Composite Index untuk Query Multi-Kolom
#

Jika query sering menggunakan lebih dari satu kolom pada klausa WHERE, composite index dapat meningkatkan performa secara signifikan.

-- Composite index
CREATE INDEX idx_status_created_at 
ON orders(status, created_at);

-- Query yang memanfaatkan index
SELECT * FROM orders
WHERE status = 'completed'
  AND created_at > '2025-01-01';

Pastikan urutan kolom index mengikuti pola query yang paling sering digunakan.

5. Pilih Jenis Index yang Tepat
#

B-Tree Index Cocok untuk:
- Range query (BETWEEN, <, >)
- ORDER BY
- LIKE 'prefix%'
Hash Index Cocok untuk:
- Exact match (=)

-- B-Tree untuk range query
CREATE INDEX idx_order_date ON orders(order_date) USING BTREE;

-- Hash untuk equality (terutama MEMORY engine)
CREATE INDEX idx_email ON users(email) USING HASH;

InnoDB secara default menggunakan B-Tree.

6. Hindari Wildcard di Awal `LIKE`
#

Wildcard di awal string (%keyword) membuat index tidak dapat digunakan.

-- Kurang optimal
SELECT * FROM products WHERE name LIKE '%phone';

-- Lebih optimal
SELECT * FROM products WHERE name LIKE 'phone%';

7. Optimalkan Klausa `ORDER BY`
#

Query dengan ORDER BY akan lebih efisien jika didukung oleh index.

-- Query
SELECT * FROM orders ORDER BY order_date;

-- Index pendukung
CREATE INDEX idx_order_date ON orders(order_date);

Jika memungkinkan, sesuaikan urutan index dengan ORDER BY dan WHERE.

8. Perhatikan Fragmentasi Index
#

Operasi INSERT, UPDATE, dan DELETE yang intensif dapat menyebabkan fragmentasi index.

-- Defragmentasi tabel dan index
OPTIMIZE TABLE users;

Gunakan dengan hati-hati pada tabel besar di production karena dapat mengunci tabel.

9. Hindari `NOT IN` dan Operator `<>`
#

Operator negatif sering menghasilkan performa buruk dan sulit dioptimasi.

-- Kurang efisien
SELECT * FROM users WHERE id NOT IN (1, 2, 3);

-- Lebih efisien (EXISTS)
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM banned_users b
    WHERE b.id = u.id
);

10. Hindari Index pada Kolom dengan Kardinalitas Rendah
#

Kolom dengan sedikit variasi nilai (low cardinality) biasanya tidak efektif untuk index tunggal.

-- Kurang efektif (nilai hanya 'M' atau 'F')
CREATE INDEX idx_gender ON users(gender);

Lebih baik:

Index kolom dengan nilai unik tinggi
Atau gunakan composite index

-- Lebih efektif
CREATE INDEX idx_email ON users(email);

-- Composite index untuk meningkatkan selektivitas
CREATE INDEX idx_gender_email ON users(gender, email);