SQL Indexes: Panduan Advanced

-- =============================================
-- SETUP DATABASE UNTUK LATIHAN INDEX
-- =============================================

CREATE TABLE orders (
    id              SERIAL PRIMARY KEY,
    customer_id     INT NOT NULL,
    customer_email  VARCHAR(200) NOT NULL,
    product_name    VARCHAR(300) NOT NULL,
    category        VARCHAR(50) NOT NULL,
    quantity        INT NOT NULL,
    unit_price      DECIMAL(12,2) NOT NULL,
    total_amount    DECIMAL(15,2) NOT NULL,
    order_status    VARCHAR(20) NOT NULL DEFAULT 'pending',
    order_date      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ship_date       TIMESTAMP,
    ship_city       VARCHAR(100),
    ship_country    VARCHAR(50),
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample data (gunakan generate_series di PostgreSQL)
INSERT INTO orders (customer_id, customer_email, product_name, category,
                    quantity, unit_price, total_amount, order_status,
                    order_date, ship_city, ship_country)
SELECT
    (random() * 10000)::int,
    'user' || (random() * 10000)::int || '@email.com',
    'Product-' || (random() * 500)::int,
    CASE (random() * 4)::int
        WHEN 0 THEN 'Elektronik'
        WHEN 1 THEN 'Fashion'
        WHEN 2 THEN 'Makanan'
        WHEN 3 THEN 'Kesehatan'
        ELSE 'Rumah'
    END,
    (random() * 10 + 1)::int,
    (random() * 500000 + 10000)::decimal(12,2),
    (random() * 5000000 + 50000)::decimal(15,2),
    CASE (random() * 3)::int
        WHEN 0 THEN 'pending'
        WHEN 1 THEN 'shipped'
        WHEN 2 THEN 'delivered'
        ELSE 'cancelled'
    END,
    CURRENT_TIMESTAMP - (random() * 365)::int * INTERVAL '1 day',
    CASE (random() * 4)::int
        WHEN 0 THEN 'Jakarta'
        WHEN 1 THEN 'Bandung'
        WHEN 2 THEN 'Surabaya'
        WHEN 3 THEN 'Yogyakarta'
        ELSE 'Medan'
    END,
    'Indonesia'
FROM generate_series(1, 500000);

-- =============================================
-- DEFAULT INDEX = B-tree
-- =============================================

-- Index sederhana pada satu kolom
CREATE INDEX idx_orders_customer_id
ON orders (customer_id);

-- Index dengan tipe eksplisit (PostgreSQL)
CREATE INDEX idx_orders_customer_email
ON orders USING btree (customer_email);

-- Index DESCENDING (untuk ORDER BY DESC yang sering)
CREATE INDEX idx_orders_date_desc
ON orders (order_date DESC);

-- Index dengan NULL handling (PostgreSQL)
CREATE INDEX idx_orders_ship_date_nulls_first
ON orders (ship_date NULLS FIRST);


-- =============================================
-- B-tree mendukung semua operator ini:
-- =============================================
-- =  (equal)
-- <  (less than)
-- <= (less than or equal)
-- >  (greater than)
-- >= (greater than or equal)
-- BETWEEN
-- IN
-- LIKE 'abc%' (prefix matching saja!)
-- IS NULL / IS NOT NULL
-- ORDER BY (ascending/descending)


-- =============================================
-- Contoh query yang MEMANFAATKAN B-tree index:
-- =============================================
SELECT * FROM orders WHERE customer_id = 1234;
SELECT * FROM orders WHERE customer_id BETWEEN 100 AND 200;
SELECT * FROM orders WHERE order_date >= '2026-01-01';
SELECT * FROM orders ORDER BY order_date DESC LIMIT 20;
SELECT * FROM orders WHERE customer_email LIKE 'user123%';  -- prefix!


-- =============================================
-- Contoh query yang TIDAK bisa pakai B-tree index:
-- =============================================
SELECT * FROM orders WHERE customer_email LIKE '%gmail.com'; -- suffix!
SELECT * FROM orders WHERE UPPER(product_name) = 'LAPTOP';  -- function!
SELECT * FROM orders WHERE total_amount + 1000 > 50000;     -- expression!

-- =============================================
-- HASH INDEX (PostgreSQL)
-- =============================================
-- PostgreSQL mendukung hash index sejak versi 10
-- (sebelumnya tidak reliable karena tidak WAL-logged)

CREATE INDEX idx_orders_email_hash
ON orders USING hash (customer_email);

-- Query yang cocok untuk hash index:
SELECT * FROM orders WHERE customer_email = 'user1234@email.com';

-- Query yang TIDAK cocok:
-- SELECT * FROM orders WHERE customer_email > 'user5000@email.com';  -- range!
-- SELECT * FROM orders ORDER BY customer_email;                       -- sort!
-- SELECT * FROM orders WHERE customer_email LIKE 'user123%';          -- prefix!


-- =============================================
-- HASH INDEX di MySQL (MEMORY engine)
-- =============================================
-- MySQL InnoDB TIDAK mendukung hash index eksplisit
-- Tapi InnoDB punya "adaptive hash index" (otomatis)
-- MEMORY engine mendukung HASH index:

CREATE TABLE sessions_memory (
    session_id  VARCHAR(128) NOT NULL,
    user_id     INT NOT NULL,
    data        TEXT,
    expires_at  TIMESTAMP NOT NULL,
    PRIMARY KEY (session_id) USING HASH
) ENGINE=MEMORY;


-- =============================================
-- PERBANDINGAN: B-tree vs Hash
-- =============================================
-- ┌──────────────┬─────────────┬─────────────┐
-- │ Operasi      │ B-tree      │ Hash        │
-- ├──────────────┼─────────────┼─────────────┤
-- │ = (equal)    │ ✅ Bagus    │ ✅ Terbaik  │
-- │ < > <= >=    │ ✅ Bagus    │ ❌ Tidak    │
-- │ BETWEEN      │ ✅ Bagus    │ ❌ Tidak    │
-- │ ORDER BY     │ ✅ Bagus    │ ❌ Tidak    │
-- │ LIKE 'abc%'  │ ✅ Bagus    │ ❌ Tidak    │
-- │ IS NULL      │ ✅ Bagus    │ ✅ Bagus    │
-- │ IN (...))    │ ✅ Bagus    │ ✅ Bagus    │
-- │ Ukuran index │ Lebih besar │ Lebih kecil │
-- │ Lookup time  │ O(log n)    │ O(1)        │
-- └──────────────┴─────────────┴─────────────┘

-- =============================================
-- MEMBUAT COMPOSITE INDEX
-- =============================================

-- Index pada category + order_date
CREATE INDEX idx_orders_cat_date
ON orders (category, order_date);

-- Index pada status + category + date
CREATE INDEX idx_orders_status_cat_date
ON orders (order_status, category, order_date);


-- =============================================
-- ATURAN "LEFTMOST PREFIX"
-- =============================================
-- Index: (category, order_date)

-- ✅ Query ini MEMANFAATKAN index (pakai kolom pertama):
SELECT * FROM orders WHERE category = 'Elektronik';
SELECT * FROM orders WHERE category = 'Elektronik' AND order_date >= '2026-01-01';
SELECT * FROM orders WHERE category = 'Elektronik' ORDER BY order_date;

-- ❌ Query ini TIDAK memanfaatkan index (skip kolom pertama):
SELECT * FROM orders WHERE order_date >= '2026-01-01';  -- tidak ada category!
SELECT * FROM orders ORDER BY order_date;               -- tidak ada category!


-- =============================================
-- URUTAN KOLOM SANGAT PENTING!
-- =============================================
-- Index (A, B) ≠ Index (B, A)

-- Index idx_cat_date = (category, order_date)
-- Mendukung: WHERE category = ?, ORDER BY order_date
-- Tidak mendukung: WHERE order_date = ? (tanpa category)

-- Index idx_date_cat = (order_date, category)
-- Mendukung: WHERE order_date = ?, ORDER BY category
-- Tidak mendukung: WHERE category = ? (tanpa order_date)


-- =============================================
-- PRINSIP: Urutkan kolom berdasarkan selektivitas
-- =============================================
-- Kolom dengan lebih banyak nilai unik (kardinalitas tinggi) → duluan
-- Contoh:
--   customer_id (jutaan unik) → SANGAT selektif → duluan
--   status (hanya 4 nilai)    → kurang selektif   → belakangan

-- ✅ BAGUS: selektif duluan
CREATE INDEX idx_orders_cust_status
ON orders (customer_id, order_status);

-- ❌ KURANG BAIUS: kurang selektif duluan
CREATE INDEX idx_orders_status_cust
ON orders (order_status, customer_id);


-- =============================================
-- GABUNGAN: Composite + Expression
-- =============================================
CREATE INDEX idx_orders_cust_lower_status
ON orders (customer_id, LOWER(order_status));

-- =============================================
-- TANPA covering index: butuh table access
-- =============================================

-- Query:
SELECT customer_id, total_amount, order_status
FROM orders
WHERE category = 'Elektronik' AND order_date >= '2026-01-01';

-- Index yang ada: idx_orders_cat_date (category, order_date)
-- Database harus:
--   1. Cari di index → dapat row pointers
--   2. Akses tabel utama (heap) untuk ambil customer_id, total_amount, order_status
-- Langkah 2 ini = "table lookup" atau "heap fetch" → LAMBAT


-- =============================================
-- DENGAN covering index: index-only scan!
-- =============================================

-- Tambahkan kolom yang dibutuhkan ke index
CREATE INDEX idx_orders_covering1
ON orders (category, order_date, customer_id, total_amount, order_status);

-- Sekarang query di atas hanya perlu baca index!
-- Tidak perlu akses tabel utama sama sekali → JAUH lebih cepat


-- =============================================
-- PostgreSQL: INCLUDE clause (lebih efisien)
-- =============================================
-- Kolom di INCLUDE tidak diurutkan, tapi tetap tersimpan di leaf nodes
-- Lebih kecil dan lebih cepat dari menambah ke key columns

CREATE INDEX idx_orders_covering2
ON orders (category, order_date)
INCLUDE (customer_id, total_amount, order_status);

-- Kolom key (diurutkan): category, order_date
-- Kolom include (hanya disimpan): customer_id, total_amount, order_status


-- =============================================
-- MySQL: Tidak ada INCLUDE, tapi composite index bisa jadi covering
-- =============================================
-- MySQL 8.0: cukup tambahkan semua kolom ke composite index
CREATE INDEX idx_orders_covering_mysql
ON orders (category, order_date, customer_id, total_amount, order_status);

-- =============================================
-- PARTIAL INDEX: Hanya untuk data yang relevan
-- =============================================

-- Misalnya 90% pesanan sudah delivered, hanya 10% yang pending
-- Query kita hampir selalu mencari yang pending
-- Index semua baris = boros, lebih baik partial index

CREATE INDEX idx_orders_pending
ON orders (customer_id, order_date)
WHERE order_status = 'pending';

-- Index hanya berisi baris WHERE order_status = 'pending'
-- → Lebih kecil → lebih cepat → lebih hemat storage


-- =============================================
-- Partial Index untuk data tidak NULL
-- =============================================

-- Hanya index data yang sudah di-shipping
CREATE INDEX idx_orders_shipped_date
ON orders (ship_date)
WHERE ship_date IS NOT NULL;


-- =============================================
-- Partial Unique Index
-- =============================================

-- Pastikan email unik HANYA untuk user aktif
CREATE UNIQUE INDEX idx_unique_active_email
ON orders (customer_email)
WHERE order_status != 'cancelled';


-- =============================================
-- Query yang cocok dengan partial index:
-- =============================================
SELECT * FROM orders
WHERE order_status = 'pending' AND customer_id = 1234;

SELECT * FROM orders
WHERE order_status = 'pending' AND order_date >= '2026-06-01';


-- =============================================
-- SQL Server: Filtered Index (sama konsep)
-- =============================================
CREATE NONCLUSTERED INDEX idx_orders_pending_sqlserver
ON orders (customer_id, order_date)
WHERE order_status = 'pending';

-- =============================================
-- MASALAH: Fungsi pada WHERE mematikan index
-- =============================================

-- Index pada customer_email ada, tapi...
SELECT * FROM orders WHERE LOWER(customer_email) = 'user1234@email.com';
-- ❌ Index TIDAK terpakai karena LOWER() pada kolom!

-- Solusi: Expression Index
CREATE INDEX idx_orders_lower_email
ON orders (LOWER(customer_email));

-- Sekarang query di atas ✅ menggunakan index!


-- =============================================
-- CONTOH LAINNYA
-- =============================================

-- Index pada tahun order
CREATE INDEX idx_orders_year
ON orders (EXTRACT(YEAR FROM order_date));

SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2026;


-- Index pada panjang nama produk
CREATE INDEX idx_orders_name_length
ON orders (LENGTH(product_name));

SELECT * FROM orders WHERE LENGTH(product_name) > 20;


-- Index pada date truncation
CREATE INDEX idx_orders_month
ON orders (DATE_TRUNC('month', order_date));

SELECT * FROM orders WHERE DATE_TRUNC('month', order_date) = '2026-06-01';


-- Index pada kombinasi kolom dengan fungsi
CREATE INDEX idx_orders_category_lower
ON orders (category, LOWER(product_name));

SELECT * FROM orders
WHERE category = 'Elektronik' AND LOWER(product_name) LIKE '%laptop%';


-- =============================================
-- MySQL 8.0: Expression Index (generated columns)
-- =============================================
-- MySQL tidak mendukung expression index langsung,
-- tapi bisa pakai generated column + index:

ALTER TABLE orders
ADD COLUMN email_lower VARCHAR(200) GENERATED ALWAYS AS (LOWER(customer_email)) STORED;

CREATE INDEX idx_email_lower_mysql ON orders (email_lower);

SELECT * FROM orders WHERE email_lower = 'user1234@email.com';

-- =============================================
-- EXPLAIN: Lihat rencana eksekusi
-- =============================================

-- PostgreSQL
EXPLAIN SELECT * FROM orders WHERE customer_id = 1234;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1234;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM orders WHERE customer_id = 1234;

-- MySQL
EXPLAIN SELECT * FROM orders WHERE customer_id = 1234;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1234;


-- =============================================
-- MEMBACA HASIL EXPLAIN (PostgreSQL)
-- =============================================
-- Cari operasi berikut di output EXPLAIN:

-- 1. Index Scan → ✅ BAGUS (menggunakan index + akses tabel)
-- 2. Index Only Scan → ✅ TERBAIK (hanya baca index, tidak akses tabel)
-- 3. Bitmap Index Scan → ✅ CUKUP BAGUS (kombinasi multiple index)
-- 4. Seq Scan → ⚠️ HATI-HATI (full table scan!)

-- Contoh output EXPLAIN ANALYZE:
/*
Seq Scan on orders  (cost=0.00..9570.00 rows=1 width=168) (actual time=0.015..45.230 rows=1 loops=1)
  Filter: (customer_id = 1234)
  Rows Removed by Filter: 499999
Planning Time: 0.050 ms
Execution Time: 45.250 ms
*/
-- ↑ Seq Scan! Tidak ada index pada customer_id yang cocok


-- Setelah membuat index:
/*
Index Scan using idx_orders_customer_id on orders  (cost=0.42..8.44 rows=1 width=168)
  (actual time=0.020..0.022 rows=1 loops=1)
  Index Cond: (customer_id = 1234)
Planning Time: 0.080 ms
Execution Time: 0.035 ms
*/
-- ↑ Index Scan! 1000x lebih cepat!


-- =============================================
-- MySQL: Membaca EXPLAIN output
-- =============================================
-- Kolom penting di EXPLAIN MySQL:
--   type:  const > eq_ref > ref > range > index > ALL
--   key:   nama index yang digunakan (NULL = tidak pakai index)
--   rows:  estimasi baris yang di-scan
--   Extra: Using index = covering index ✅
--          Using filesort = perlu sort manual ⚠️
--          Using temporary = buat tabel sementara ⚠️

EXPLAIN SELECT * FROM orders
WHERE category = 'Elektronik' AND order_date >= '2026-01-01';
-- Cek kolom "type": ref = bagus, ALL = buruk!
-- Cek kolom "key": idx_orders_cat_date = index digunakan ✅


-- =============================================
-- STATISTICS: Update statistik untuk query plan akurat
-- =============================================
-- PostgreSQL:
ANALYZE orders;

-- MySQL:
ANALYZE TABLE orders;

-- ❌ SALAH: Membuat index untuk setiap kolom
CREATE INDEX idx_1 ON orders (customer_id);
CREATE INDEX idx_2 ON orders (order_date);
CREATE INDEX idx_3 ON orders (category);
CREATE INDEX idx_4 ON orders (order_status);
CREATE INDEX idx_5 ON orders (ship_city);
CREATE INDEX idx_6 ON orders (total_amount);
-- Setiap INSERT/UPDATE/DELETE = update 6 index! Sangat lambat!

-- ✅ BENAR: Composite index yang dirancang untuk query spesifik
CREATE INDEX idx_main ON orders (customer_id, order_date);
CREATE INDEX idx_cat_status ON orders (category, order_status, order_date);

-- Cara cek index yang ada:
-- PostgreSQL:
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';

-- MySQL:
SHOW INDEX FROM orders;


-- =============================================
-- CARI INDEX YANG TIDAK PERNAH DIPAKAI
-- =============================================

-- PostgreSQL: Index usage statistics
SELECT
    schemaname, relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC;
-- Index dengan idx_scan = 0 → TIDAK PERNAH dipakai → hapus!

-- ❌ Index kurang selektif duluan
CREATE INDEX idx_bad ON orders (order_status, category, customer_id);
-- order_status hanya punya 4 nilai → tidak selektif

-- ✅ Index selektif duluan
CREATE INDEX idx_good ON orders (customer_id, category, order_status);
-- customer_id punya ribuan nilai → sangat selektif

-- Kecuali query Anda SELALU filter order_status duluan,
-- maka urutan di index harus mengikuti urutan WHERE clause


-- =============================================
-- Cek selektivitas kolom
-- =============================================
SELECT
    'customer_id' AS kolom,
    COUNT(DISTINCT customer_id) AS nilai_unik,
    COUNT(*) AS total_baris,
    ROUND(COUNT(DISTINCT customer_id) * 100.0 / COUNT(*), 2) AS selektivitas_pct
FROM orders
UNION ALL
SELECT
    'category',
    COUNT(DISTINCT category),
    COUNT(*),
    ROUND(COUNT(DISTINCT category) * 100.0 / COUNT(*), 2)
FROM orders
UNION ALL
SELECT
    'order_status',
    COUNT(DISTINCT order_status),
    COUNT(*),
    ROUND(COUNT(DISTINCT order_status) * 100.0 / COUNT(*), 2)
FROM orders;
-- customer_id: ~2% selektif (sangat bagus → duluan)
-- category: ~0.001% selektif (kurang bagus → belakangan)
-- order_status: ~0.0008% selektif (sangat kurang → paling belakang)

-- ❌ Fungsi pada kolom mematikan index
SELECT * FROM orders WHERE YEAR(order_date) = 2026;
SELECT * FROM orders WHERE DATE(order_date) = '2026-06-26';
SELECT * FROM orders WHERE UPPER(category) = 'ELEKTRONIK';
SELECT * FROM orders WHERE total_amount * 1.1 > 1000000;

-- ✅ Rewrite tanpa fungsi pada kolom
SELECT * FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';
SELECT * FROM orders WHERE order_date >= '2026-06-26' AND order_date < '2026-06-27';
SELECT * FROM orders WHERE category = 'Elektronik';  -- asumsi data konsisten
SELECT * FROM orders WHERE total_amount > 1000000 / 1.1;  -- pindahkan ke konstanta

-- Atau gunakan expression index jika memang butuh fungsi:
CREATE INDEX idx_orders_year ON orders (EXTRACT(YEAR FROM order_date));

-- =============================================
-- SKENARIO: Dashboard admin e-commerce
-- Query lambat, perlu dioptimasi
-- =============================================

-- Query 1: Laporan penjualan per kategori bulan ini
-- SEBELUM (lambat - 3 detik):
SELECT category, COUNT(*) AS orders, SUM(total_amount) AS revenue
FROM orders
WHERE order_date >= '2026-06-01' AND order_date < '2026-07-01'
  AND order_status != 'cancelled'
GROUP BY category;

-- Analisis EXPLAIN: Seq Scan + filesort
-- Solusi: Composite index yang mencakup semua kebutuhan
CREATE INDEX idx_orders_report_cat
ON orders (order_date, order_status, category, total_amount);

-- SESUDAH (cepat - 50ms): Index Only Scan!


-- Query 2: Detail pesanan customer tertentu
-- SEBELUM (lambat - 1.5 detik):
SELECT * FROM orders
WHERE customer_id = 5678
ORDER BY order_date DESC
LIMIT 20;

-- Solusi: Index dengan sort order
CREATE INDEX idx_orders_cust_date_desc
ON orders (customer_id, order_date DESC);

-- SESUDAH (cepat - 2ms): Index Scan dengan sort sudah tersedia


-- Query 3: Cari pesanan berdasarkan email
-- SEBELUM:
SELECT * FROM orders WHERE customer_email = 'user5678@email.com';

-- Solusi: Hash index (kalau hanya equality) atau B-tree
CREATE INDEX idx_orders_email ON orders (customer_email);

-- SESUDAH (cepat - 1ms): Index Scan


-- =============================================
-- MONITORING: Index bloat (PostgreSQL)
-- =============================================
SELECT
    schemaname, tablename, indexname,
    pg_size_pretty(pg_relation_size(indexid)) AS index_size,
    idx_scan AS scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexid) DESC;


-- =============================================
-- MAINTENANCE: Reindex (PostgreSQL)
-- =============================================
-- Index bisa "bloat" seiring waktu (terutama B-tree)
-- Lakukan REINDEX secara berkala:
REINDEX INDEX idx_orders_cat_date;
-- Atau untuk semua index di tabel:
REINDEX TABLE orders;

-- PostgreSQL modern: gunakan CONCURRENTLY agar tidak lock tabel
REINDEX INDEX CONCURRENTLY idx_orders_cat_date;