SQL Query Optimization: Panduan Lengkap EXPLAIN, Indexes & Query Plans

-- =============================================
-- Setup: Buat tabel untuk contoh
-- =============================================
CREATE TABLE orders (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    product_id  INT NOT NULL,
    amount      DECIMAL(12,2),
    status      VARCHAR(20),
    order_date  DATE,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample data (1 juta baris)
-- ... bulk insert ...

-- =============================================
-- EXPLAIN Dasar: Lihat query plan
-- =============================================
EXPLAIN SELECT * FROM orders WHERE customer_id = 1234;

-- Hasil:
-- +----+------+------+------+----------+------+
-- | id | type | key  | rows | filtered | Extra|
-- +----+------+------+------+----------+------+
-- |  1 | ALL  | NULL | 1000000| 10.00  |Using where|
-- +----+------+------+------+----------+------+
-- type=ALL → Full table scan! Sangat lambat pada tabel besar.


-- =============================================
-- EXPLAIN ANALYZE (MySQL 8.0.18+)
-- =============================================
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 1234;
-- Menampilkan actual execution time, bukan hanya estimate


-- =============================================
-- EXPLAIN FORMAT=JSON
-- =============================================
EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE customer_id = 1234;
-- Output JSON detail — bisa di-parse untuk analisis otomatis


-- =============================================
-- EXPLAIN FORMAT=TREE (MySQL 8.0.16+)
-- =============================================
EXPLAIN FORMAT=TREE
SELECT o.*, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'shipped';

-- =============================================
-- INDEX DASAR: Single Column Index
-- =============================================

-- Buat index pada kolom customer_id
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- Sekarang query ini jauh lebih cepat:
EXPLAIN SELECT * FROM orders WHERE customer_id = 1234;
-- type: ref, key: idx_orders_customer_id → Menggunakan index!

-- =============================================
-- INDEX pada kolom yang sering di-SEARCH
-- =============================================

-- Index untuk pencarian berdasarkan status
CREATE INDEX idx_orders_status ON orders(status);

-- Index untuk pencarian berdasarkan tanggal
CREATE INDEX idx_orders_date ON orders(order_date);

-- Index untuk pencarian berdasarkan product
CREATE INDEX idx_orders_product ON orders(product_id);


-- =============================================
-- UNIQUE INDEX: Menjamin keunikan
-- =============================================
CREATE UNIQUE INDEX idx_customers_email ON customers(email);

-- UNIQUE INDEX berfungsi ganda:
-- 1. Mempercepat pencarian berdasarkan email
-- 2. Mencegah duplikat email


-- =============================================
-- MENGECEK INDEX yang ada pada tabel
-- =============================================
-- MySQL:
SHOW INDEX FROM orders;

-- PostgreSQL:
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';


-- =============================================
-- MENGHAPUS INDEX yang tidak diperlukan
-- =============================================
DROP INDEX idx_orders_status ON orders;

-- Atau dengan aman (MySQL):
DROP INDEX IF EXISTS idx_orders_status ON orders;


-- =============================================
-- INDEX pada kolom yang sering di-ORDER BY
-- =============================================
-- Query ini akan benefit dari index:
SELECT * FROM orders ORDER BY order_date DESC LIMIT 20;

-- Buat index:
CREATE INDEX idx_orders_date_desc ON orders(order_date DESC);

-- =============================================
-- COMPOSITE INDEX: Multi-kolom
-- =============================================
CREATE INDEX idx_orders_cust_status_date
ON orders(customer_id, status, order_date);

-- Query yang TERBANTU oleh index ini:
-- ✅ WHERE customer_id = 1234
-- ✅ WHERE customer_id = 1234 AND status = 'shipped'
-- ✅ WHERE customer_id = 1234 AND status = 'shipped' AND order_date > '2026-01-01'
-- ✅ ORDER BY customer_id, status, order_date

-- Query yang TIDAK terbantu:
-- ❌ WHERE status = 'shipped'  (tidak pakai prefix customer_id)
-- ❌ WHERE order_date > '2026-01-01' (tidak pakai prefix)


-- =============================================
-- LEFTMOST PREFIX RULE
-- =============================================
-- Index: (A, B, C) bisa dipakai untuk:
-- ✅ (A)         — prefix pertama
-- ✅ (A, B)      — dua prefix pertama
-- ✅ (A, B, C)   — ketiganya
-- ❌ (B, C)      — skip A
-- ❌ (C)         — skip A dan B


-- =============================================
-- COVERING INDEX: Semua kolom ada di index
-- =============================================
-- Query ini hanya butuh kolom: customer_id, status, amount
SELECT customer_id, status, amount
FROM orders
WHERE customer_id = 1234 AND status = 'shipped';

-- Covering index yang ideal:
CREATE INDEX idx_orders_covering
ON orders(customer_id, status, amount);

-- Dengan covering index, database TIDAK perlu mengakses
-- data rows sama sekali (Index-Only Scan)
-- EXPLAIN akan menampilkan: "Using index"


-- =============================================
-- INDEX MERGE (MySQL)
-- =============================================
-- Kadang MySQL bisa pakai 2 index sekaligus:
-- Index: idx_customer, idx_status
SELECT * FROM orders
WHERE customer_id = 1234 OR status = 'shipped';
-- EXPLAIN mungkin menampilkan: "Using union(idx_customer, idx_status)"

-- Tapi lebih efisien dengan composite index:
CREATE INDEX idx_cust_status ON orders(customer_id, status);

-- =============================================
-- QUERY PLAN: Contoh Analisis
-- =============================================

-- Query yang kompleks:
EXPLAIN ANALYZE
SELECT
    c.name AS customer_name,
    COUNT(o.id) AS total_orders,
    SUM(o.amount) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.order_date >= '2026-01-01'
  AND o.status IN ('shipped', 'delivered')
GROUP BY c.id, c.name
HAVING COUNT(o.id) > 5
ORDER BY total_spent DESC
LIMIT 10;

-- Analisis Output EXPLAIN:
-- 1. Join Order: customers → orders (atau sebaliknya)
-- 2. Join Method: Hash Join vs Nested Loop vs Merge Join
-- 3. Access Path: Index Scan vs Seq Scan
-- 4. Filter: WHERE diterapkan sebelum/sesudah join
-- 5. Sort: In-memory sort vs filesort
-- 6. Aggregation: HashAggregate vs GroupAggregate


-- =============================================
-- POSTGRESQL: EXPLAIN ANALYZE VERBOSE
-- =============================================
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE customer_id = 1234
ORDER BY order_date DESC
LIMIT 10;

-- Output:
-- Limit  (cost=0.43..8.45 rows=10 width=56) (actual time=0.025..0.032 rows=10)
--   ->  Index Scan using idx_orders_cust_date on orders
--       (cost=0.43..805.43 rows=1000 width=56) (actual time=0.024..0.030 rows=10)
--       Index Cond: (customer_id = 1234)
--       Buffers: shared hit=4
-- Planning Time: 0.150 ms
-- Execution Time: 0.045 ms


-- =============================================
-- JOIN METHODS: Kapan Digunakan
-- =============================================
-- Nested Loop Join:
--   Cocok: Tabel kecil, ada index pada join column
--   Contoh: customers (100 rows) JOIN orders dengan index

-- Hash Join:
--   Cocok: Tabel besar, TANPA index, equality join
--   Contoh: 2 tabel besar JOIN dengan =

-- Merge Join:
--   Cocok: Kedua tabel sudah ter-sort pada join column
--   Contoh: Tabel yang di-ORDER BY kolom join

-- =============================================
-- 1. HINDARI FUNCTION pada indexed column
-- =============================================

-- ❌ BURUK: Index tidak bisa dipakai karena YEAR()
SELECT * FROM orders WHERE YEAR(order_date) = 2026;

-- ✅ BAIK: Gunakan range pada kolom asli
SELECT * FROM orders
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';

-- ❌ BURUK: LOWER() pada indexed column
SELECT * FROM customers WHERE LOWER(email) = 'test@example.com';

-- ✅ BAIK: Simpan data sudah lowercase, atau buat expression index (PG)
CREATE INDEX idx_customers_email_lower
ON customers(LOWER(email));  -- PostgreSQL expression index


-- =============================================
-- 2. HINDARI SELECT * — ambil kolom yang diperlukan
-- =============================================

-- ❌ BURUK: Mengambil semua kolom termasuk TEXT/BLOB besar
SELECT * FROM orders WHERE customer_id = 1234;

-- ✅ BAIK: Ambil hanya kolom yang dibutuhkan
SELECT id, amount, status, order_date
FROM orders
WHERE customer_id = 1234;
-- Bisa memanfaatkan covering index!


-- =============================================
-- 3. OPTIMASI LIKE
-- =============================================

-- ❌ BURUK: Leading wildcard — index tidak dipakai
SELECT * FROM products WHERE nama_produk LIKE '%laptop%';

-- ✅ BAIK: Trailing wildcard — index bisa dipakai
SELECT * FROM products WHERE nama_produk LIKE 'laptop%';

-- ✅ ALTERNATIF: Full-text search untuk pencarian bebas
SELECT * FROM products
WHERE MATCH(nama_produk) AGAINST('laptop gaming' IN BOOLEAN MODE);


-- =============================================
-- 4. OPTIMASI IN dengan banyak nilai
-- =============================================

-- ❌ BURUK: Terlalu banyak nilai dalam IN
SELECT * FROM orders
WHERE customer_id IN (1,2,3,...,10000);

-- ✅ BAIK: Gunakan JOIN dengan temporary table
CREATE TEMPORARY TABLE tmp_ids (id INT PRIMARY KEY);
-- INSERT ke tmp_ids ...
SELECT o.* FROM orders o
JOIN tmp_ids t ON o.customer_id = t.id;


-- =============================================
-- 5. OPTIMASI COUNT
-- =============================================

-- ❌ LAMBAT: COUNT(*) pada tabel besar tanpa filter
SELECT COUNT(*) FROM orders;
-- Scans entire table

-- ✅ LEBIH CEPAT: Hitung estimasi dari metadata (MySQL)
SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'orders';

-- ✅ LEBIH CEPAT: Hitung estimasi (PostgreSQL)
SELECT reltuples::bigint AS estimate
FROM pg_class WHERE relname = 'orders';


-- =============================================
-- 6. EXISTS vs IN untuk subquery
-- =============================================

-- ❌ IN: Memproses semua hasil subquery
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE amount > 1000000);

-- ✅ EXISTS: Berhenti begitu menemukan match pertama
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id AND o.amount > 1000000
);

-- =============================================
-- CONTOH 1: Cari pelanggan yang pernah beli
-- =============================================

-- Menggunakan Subquery (correlated):
SELECT nama, email FROM pelanggan p
WHERE EXISTS (
    SELECT 1 FROM pesanan ps
    WHERE ps.id_pelanggan = p.id_pelanggan
    AND ps.total > 500000
);
-- ✅ Bagus untuk: mengecek keberadaan (EXISTS)
-- Correlated subquery dijalankan per baris outer

-- Menggunakan JOIN:
SELECT DISTINCT p.nama, p.email
FROM pelanggan p
JOIN pesanan ps ON ps.id_pelanggan = p.id_pelanggan
WHERE ps.total > 500000;
-- ✅ Bagus untuk: mengambil data dari tabel lain
-- DISTINCT diperlukan karena JOIN bisa menghasilkan duplikat


-- =============================================
-- CONTOH 2: Agregasi per grup
-- =============================================

-- Subquery dalam FROM (Derived Table):
SELECT p.nama, sub.total_belanja
FROM pelanggan p
JOIN (
    SELECT id_pelanggan, SUM(total) AS total_belanja
    FROM pesanan
    GROUP BY id_pelanggan
) sub ON p.id_pelanggan = sub.id_pelanggan
WHERE sub.total_belanja > 1000000;

-- JOIN langsung dengan HAVING:
SELECT p.nama, SUM(ps.total) AS total_belanja
FROM pelanggan p
JOIN pesanan ps ON p.id_pelanggan = ps.id_pelanggan
GROUP BY p.id_pelanggan, p.nama
HAVING SUM(ps.total) > 1000000;


-- =============================================
-- CONTOH 3: Window Function (Modern SQL)
-- =============================================
-- Lebih efisien dari subquery untuk ranking:
SELECT nama, total_belanja, ranking
FROM (
    SELECT
        p.nama,
        SUM(ps.total) AS total_belanja,
        RANK() OVER (ORDER BY SUM(ps.total) DESC) AS ranking
    FROM pelanggan p
    JOIN pesanan ps ON p.id_pelanggan = ps.id_pelanggan
    GROUP BY p.id_pelanggan, p.nama
) ranked
WHERE ranking <= 10;

-- =============================================
-- MYSQL: Mengaktifkan Slow Query Log
-- =============================================

-- Cek status slow query log
SHOW VARIABLES LIKE 'slow_query_log';

-- Aktifkan (sementara, hilang saat restart)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;        -- Query > 1 detik
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Aktifkan permanen: tambahkan di my.cnf
-- [mysqld]
-- slow_query_log = 1
-- slow_query_log_file = /var/log/mysql/slow.log
-- long_query_time = 1
-- log_queries_not_using_indexes = 1


-- =============================================
-- MYSQL: Performance Schema
-- =============================================
-- Query paling lambat (MySQL 8+)
SELECT
    DIGEST_TEXT AS query_pattern,
    COUNT_STAR AS exec_count,
    ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms,
    ROUND(MAX_TIMER_WAIT / 1000000000, 2) AS max_ms,
    SUM_ROWS_EXAMINED AS rows_scanned,
    SUM_ROWS_SENT AS rows_returned
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;


-- =============================================
-- POSTGRESQL: pg_stat_statements
-- =============================================
-- Pastikan extension aktif:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Query paling lambat:
SELECT
    query,
    calls,
    ROUND(mean_exec_time::numeric, 2) AS avg_ms,
    ROUND(max_exec_time::numeric, 2) AS max_ms,
    rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;


-- =============================================
-- MONITORING: Cek index usage
-- =============================================

-- MySQL: Index yang tidak pernah dipakai
SELECT
    object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND index_name != 'PRIMARY'
  AND count_star = 0
ORDER BY object_schema, object_name;

-- PostgreSQL: Index yang jarang dipakai
SELECT
    schemaname, tablename, indexname,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan < 10
ORDER BY pg_relation_size(indexrelid) DESC;

-- =============================================
-- ANTI-PATTERN 1: OFFSET besar
-- =============================================

-- ❌ BURUK: Halaman 1000 dengan 10 per halaman
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 10000;
-- Database membaca 10,010 baris, buang 10,000

-- ✅ BAIK: Keyset pagination
SELECT * FROM orders
WHERE id > 10000  -- ID terakhir dari halaman sebelumnya
ORDER BY id
LIMIT 10;
-- Hanya membaca 10 baris!


-- =============================================
-- ANTI-PATTERN 2: N+1 Query Problem
-- =============================================
-- ❌ BURUK (dalam aplikasi):
-- 1 query: SELECT * FROM customers
-- N query: SELECT * FROM orders WHERE customer_id = ?
-- (1000 customers = 1001 query!)

-- ✅ BAIK: Batch dengan JOIN atau IN
-- 1 query: SELECT c.*, o.* FROM customers c
--          JOIN orders o ON c.id = o.customer_id


-- =============================================
-- ANTI-PATTERN 3: Index berlebihan
-- =============================================
-- ❌ BURUK: Terlalu banyak index
-- INSERT/UPDATE jadi lambat karena setiap index di-update
CREATE INDEX idx_a ON orders(customer_id);
CREATE INDEX idx_b ON orders(status);
CREATE INDEX idx_c ON orders(order_date);
CREATE INDEX idx_d ON orders(amount);
CREATE INDEX idx_e ON orders(product_id);
-- 5 index = 5x overhead saat INSERT!

-- ✅ BAIK: Composite index yang terencana
CREATE INDEX idx_main ON orders(customer_id, status, order_date);
-- 1 index yang mendukung berbagai pola query


-- =============================================
-- ANTI-PATTERN 4: Menggunakan ORM tanpa cek query
-- =============================================
-- ORM seperti Hibernate / ActiveRecord bisa generate query buruk
-- Selalu cek query yang di-generate di development!
-- Aktifkan query logging pada framework Anda


-- =============================================
-- ANTI-PATTERN 5: Tidak pakai parameterized query
-- =============================================
-- ❌ BURUK: String concatenation (SQL Injection risk + no plan cache)
-- "SELECT * FROM orders WHERE id = " + userInput

-- ✅ BAIK: Parameterized / prepared statement
-- "SELECT * FROM orders WHERE id = ?"