Mengoptimasi query MySQL dengan menggunakan EXPLAIN adalah langkah penting untuk memastikan query dijalankan dengan efisien. Berikut panduan penggunaan dan langkah optimasi dengan EXPLAIN:

1. Penggunaan EXPLAIN

Jalankan query Anda dengan menambahkan kata kunci EXPLAIN di depannya:

EXPLAIN SELECT  *  FROM orders WHERE customer_id =  123; 

Hasilnya akan memberikan informasi tentang bagaimana MySQL menjalankan query Anda.

2. Kolom Penting dalam EXPLAIN

Hasil EXPLAIN terdiri dari beberapa kolom, di antaranya:

| Kolom          | Deskripsi                                                                                      |
|----------------+------------------------------------------------------------------------------------------------|
| id         	| ID langkah query. Semakin besar nilainya, semakin dalam level eksekusi.                        |
| select_type	| Jenis query (SIMPLE, PRIMARY, SUBQUERY, dll).                                                  |
| table      	| Nama tabel yang diakses.                                                                       |
| type       	| Jenis join yang digunakan. (ALL, INDEX, RANGE, dll). Jenis terbaik biasanya `const` atau `ref`.|
| possible_keys  | Indeks yang dapat digunakan.                                                               	|
| key        	| Indeks yang dipilih MySQL.                                                                     |
| key_len    	| Panjang indeks yang digunakan.                                                                 |
| ref        	| Kolom yang dibandingkan dengan indeks.                                                         |
| rows       	| Perkiraan jumlah baris yang akan dibaca.                                                       |
| filtered   	| Persentase baris yang memenuhi kondisi `WHERE`.                                                |
| Extra      	| Informasi tambahan seperti penggunaan file sort atau temporary tables.                         |

3. Strategi Optimasi

a. Gunakan Indeks
Jika kolom yang digunakan dalam kondisi WHERE, JOIN, atau ORDER BY tidak memiliki indeks, buat indeks untuk kolom tersebut:

CREATE  INDEX  idx_customer_id  ON orders (customer_id);

b. Minimalkan Jenis Join yang Mahal Jenis join seperti ALL pada kolom type menunjukkan full table scan. Gantilah dengan ref atau index menggunakan indeks yang tepat.

c. Gunakan Limitasi Data Jika memungkinkan, gunakan LIMIT untuk membatasi jumlah data yang diambil:

SELECT  *  FROM orders WHERE customer_id =  123  LIMIT  10;

d. Gabungkan Indexes dan Conditions Jika query melibatkan beberapa kolom dalam kondisi, gunakan indeks komposit:

CREATE  INDEX  idx_customer_order_date  ON orders (customer_id, order_date);

e. Kurangi Penggunaan Fungsi pada Kolom Hindari menggunakan fungsi pada kolom dalam WHERE, karena ini mencegah penggunaan indeks:

-- Tidak optimal
SELECT  *  FROM orders WHERE  YEAR(order_date) =  2024;
-- Optimal
SELECT  *  FROM orders WHERE order_date BETWEEN  '2024-01-01'  AND  '2024-12-31';

f. Optimasi Join Gunakan indeks pada kolom yang terlibat dalam join:

EXPLAIN
SELECT  *
FROM customers c
JOIN orders o ON  c.customer_id  =  o.customer_id;

Pastikan kolom customer_id di kedua tabel memiliki indeks.

g. Periksa dan Hindari Temporary Tables Jika kolom Extra menunjukkan penggunaan Using temporary, coba optimalkan query dengan indeks yang lebih baik.

4. Contoh Query EXPLAIN

EXPLAIN
SELECT  *
FROM orders
WHERE customer_id =  123
AND order_date >  '2024-01-01'
ORDER BY order_date;

Output

| id  | select_type | table  | type | possible_keys   | key             | rows | Extra                          |
|-----+-------------+--------+------+-----------------+-----------------+------+--------------------------------|
| 1   | SIMPLE      | orders | ref  | idx_customer_id | idx_customer_id | 500  | Using where; Using filesort    |

Analisis type: ref: Sudah menggunakan indeks. Extra: Using filesort: Menunjukkan MySQL perlu mengurutkan data. Buat indeks tambahan pada kolom order_date.

Dengan langkah-langkah ini, Anda dapat mengoptimalkan query MySQL untuk meningkatkan performa database Anda.