Index with Sort #

-- Query yang memicu filesort
EXPLAIN SELECT id, title, created_at
FROM articles
WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 20;

-- Jika index yang ada hanya: INDEX (status)
-- +------+-------+---------------+------+---------+--------------------------+
-- | type | key   | possible_keys | rows | filtered | Extra                   |
-- +------+-------+---------------+------+---------+--------------------------+
-- | ref  | idx_  | idx_status    | 85000| 100.00  | Using where; Using      |
-- |      | status|               |      |         | filesort                |
-- +------+-------+---------------+------+---------+--------------------------+

-- "Using where" → filter status dipakai
-- "Using filesort" → database mengumpulkan 85.000 baris,
--   lalu mengurutkan semuanya untuk mengambil 20 teratas
-- LIMIT 20 tidak membantu mengurangi biaya sort!

-- Buat composite index yang mencakup WHERE dan ORDER BY
CREATE INDEX idx_articles_status_created
    ON articles (status, created_at DESC);

-- Query yang sama sekarang:
EXPLAIN SELECT id, title, created_at
FROM articles
WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 20;

-- +-------+-----------------------------+------+---------+-----------------------+
-- | type  | key                         | rows | filtered | Extra                |
-- +-------+-----------------------------+------+---------+-----------------------+
-- | range | idx_articles_status_created |   20 | 100.00  | Using index condition |
-- +-------+-----------------------------+------+---------+-----------------------+

-- rows = 20  ← database hanya membaca 20 baris!
-- Tidak ada "Using filesort"
-- Extra: "Using index condition" → index scan dengan kondisi

-- Query: artikel published dari kategori tertentu, diurutkan terbaru
SELECT id, title, created_at
FROM articles
WHERE status = 'published'
  AND category_id = 5
ORDER BY created_at DESC
LIMIT 20;

-- ANTI-PATTERN 1: sort column di depan equality column
CREATE INDEX idx_wrong_1 ON articles (created_at DESC, status, category_id);
-- → Database tidak bisa pakai index untuk filter status dan category_id
--   karena mereka bukan prefix dari index
-- → Filesort muncul

-- ANTI-PATTERN 2: sort column di tengah equality columns
CREATE INDEX idx_wrong_2 ON articles (status, created_at DESC, category_id);
-- → Database bisa pakai status, tapi setelah bertemu created_at (sort column)
--   ia tidak bisa lanjut ke category_id di posisi berikutnya
-- → category_id tidak bisa dipakai lewat index ini
-- → Filesort bisa muncul

-- BENAR: semua equality column dulu, sort column terakhir
CREATE INDEX idx_correct ON articles (status, category_id, created_at DESC);
-- → Database: seek ke (status='published', category_id=5)
-- → Dalam posisi itu, baris sudah terurut by created_at DESC
-- → Ambil 20, selesai — tanpa filesort

-- Query: artikel yang dibuat dalam 7 hari terakhir, diurutkan terbaru
SELECT id, title, created_at
FROM articles
WHERE status = 'published'
  AND created_at >= NOW() - INTERVAL 7 DAY  -- ← range condition
ORDER BY created_at DESC
LIMIT 20;

-- Index: (status, created_at DESC)
-- Apa yang terjadi?
-- Database seek ke status='published', created_at >= [threshold]
-- Dalam rentang tersebut, data SUDAH terurut by created_at DESC
-- → Tidak ada filesort! Range pada kolom sort yang sama tidak bermasalah.

-- Tapi jika range bukan pada kolom sort:
SELECT id, title, created_at
FROM articles
WHERE status = 'published'
  AND price BETWEEN 100000 AND 500000  -- ← range pada kolom LAIN
ORDER BY created_at DESC
LIMIT 20;

-- Index ideal: (status, price, created_at DESC)
-- Masalah: setelah menemukan baris dalam range price,
-- baris tersebut TIDAK lagi terurut by created_at
-- Database masih harus sort → filesort muncul
-- Ini adalah batasan fundamental B-Tree index

-- MySQL 8.0+: descending index per kolom
CREATE INDEX idx_articles_price_created
    ON articles (price ASC, created_at DESC);

-- Query yang memanfaatkan ini:
SELECT id, title, price, created_at
FROM articles
WHERE status = 'published'
ORDER BY price ASC, created_at DESC
LIMIT 20;

-- EXPLAIN di MySQL 8.0+:
-- Extra: "Using index condition"  ← tidak ada filesort!

-- Di MySQL 5.7 atau lebih lama:
-- DESC di index diabaikan, semua disimpan ASC
-- Query ORDER BY price ASC, created_at DESC akan memicu filesort
-- karena index tidak menyimpan created_at dalam urutan DESC

-- Cek apakah index benar-benar disimpan sebagai DESC
SHOW CREATE TABLE articles;
-- Lihat apakah ada DESC di definisi index:
-- KEY `idx_articles_price_created` (`price`,`created_at` DESC)

-- PostgreSQL: bisa tentukan arah per kolom
CREATE INDEX idx_articles_price_created
    ON articles (price ASC NULLS LAST, created_at DESC NULLS FIRST);

-- NULLS FIRST / NULLS LAST menentukan posisi nilai NULL dalam sort
-- Penting untuk mencegah "mismatch sort" yang tetap memicu sort runtime

-- Query:
SELECT id, title, price, created_at
FROM articles
WHERE status = 'published'
ORDER BY price ASC NULLS LAST, created_at DESC NULLS FIRST
LIMIT 20;

-- EXPLAIN ANALYZE:
-- -> Index Scan using idx_articles_price_created on articles
--    (cost=0.43..95.23 rows=20 width=44) (actual time=0.028..0.089 rows=20 loops=1)
-- Tidak ada Sort node → index scan langsung menghasilkan urutan yang benar

-- Index: (status, created_at) — implisit ASC
CREATE INDEX idx_articles_status_created ON articles (status, created_at);

-- Query ASC: forward scan
SELECT id FROM articles
WHERE status = 'published'
ORDER BY created_at ASC
LIMIT 20;
-- → Scan index dari depan: efisien

-- Query DESC: backward scan
SELECT id FROM articles
WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 20;
-- → Scan index dari belakang: hampir sama efisiennya
-- EXPLAIN Extra: "Backward index scan" (MySQL 8.0+)
-- atau "Index Scan Backward" (PostgreSQL)
-- Tidak ada filesort!

-- Query yang sering dijalankan: list artikel terbaru dengan data minimal
SELECT id, title, status, created_at
FROM articles
WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 20;

-- Index biasa: (status, created_at DESC)
-- Database: pakai index untuk seek dan sort
-- Tapi masih perlu baca tabel untuk kolom 'title' dan 'id'
-- Extra: "Using index condition" (masih ada table lookup)

-- Covering index: tambahkan semua kolom SELECT
CREATE INDEX idx_articles_covering
    ON articles (status, created_at DESC, title, id);
-- Atau lebih ringkas karena 'id' adalah primary key yang selalu ada di index:
CREATE INDEX idx_articles_covering
    ON articles (status, created_at DESC, title);

-- Sekarang:
-- Extra: "Using index"  ← index-only scan, tidak ada table lookup
-- Rows dibaca: tepat 20 — tidak lebih

-- Perbandingan performa:
-- Tanpa covering:  baca 20 entri index + 20 lookup ke tabel
-- Dengan covering: baca 20 entri index saja — 2× lebih cepat untuk heavy read

-- Query: 20 aktivitas terbaru dari satu user
SELECT id, type, message, created_at
FROM activity_logs
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

-- ANTI-PATTERN: index hanya di user_id
CREATE INDEX idx_logs_user ON activity_logs (user_id);
-- → Filter user_id: ✓ (mungkin 50.000 log untuk user ini)
-- → Sort created_at: ✗ (filesort 50.000 baris)

-- BENAR: composite index user_id + created_at
CREATE INDEX idx_logs_user_created ON activity_logs (user_id, created_at DESC);
-- → Seek ke user_id=42, baris sudah terurut by created_at DESC
-- → Ambil 20, selesai — zero filesort
-- → rows di EXPLAIN: 20 (bukan 50.000)

-- Query: order terbaru dengan filter status dan tenant
SELECT id, order_number, total, created_at
FROM orders
WHERE tenant_id = 'abc123'
  AND status    = 'pending'
ORDER BY created_at DESC
LIMIT 20;

-- Index: (tenant_id, status, created_at DESC)
-- ✓ Equality: tenant_id
-- ✓ Equality: status
-- ✓ Sort: created_at DESC — sudah di posisi terakhir, tepat

-- Verifikasi dengan EXPLAIN:
EXPLAIN SELECT id, order_number, total, created_at
FROM orders
WHERE tenant_id = 'abc123' AND status = 'pending'
ORDER BY created_at DESC LIMIT 20;

-- Yang diharapkan:
-- type: range atau ref
-- key: idx_orders_tenant_status_created
-- rows: ≈ 20 (bukan ribuan)
-- Extra: "Using index condition" (tidak ada "Using filesort")

-- Query: produk aktif diurutkan berdasarkan featured_at (bisa NULL)
-- featured_at NULL berarti produk tidak di-feature
SELECT id, name, featured_at
FROM products
WHERE status = 'active'
ORDER BY featured_at DESC  -- NULL akan di-bawah
LIMIT 20;

-- Masalah: NULLS dalam sort bisa menyebabkan sort runtime
-- Di MySQL: NULL dianggap paling kecil (muncul terakhir di DESC)
-- Di PostgreSQL: bisa dikontrol dengan NULLS FIRST / NULLS LAST

-- Index yang tepat di MySQL:
CREATE INDEX idx_products_status_featured
    ON products (status, featured_at DESC);
-- NULL akan ada di bagian akhir → produk featured muncul dulu

-- Di PostgreSQL dengan NULLS LAST:
CREATE INDEX idx_products_status_featured
    ON products (status, featured_at DESC NULLS LAST);
-- Pastikan query menggunakan ORDER BY featured_at DESC NULLS LAST
-- agar sesuai dengan index dan tidak memicu sort runtime

-- MySQL: aktifkan slow query log dengan minimal data
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.1;  -- log query > 100ms
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Kemudian analisis dengan:
-- mysqldumpslow -s t /var/log/mysql/slow.log
-- Cari query dengan "filesort" di EXPLAIN-nya

-- PostgreSQL: pg_stat_statements untuk query terlambat
SELECT query,
       calls,
       total_exec_time / calls AS avg_ms,
       rows / calls AS avg_rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
-- Jalankan EXPLAIN ANALYZE untuk setiap query yang curigai

-- Sinyal masalah di EXPLAIN MySQL:
-- Extra: "Using filesort"           → sort runtime, bukan index sort
-- Extra: "Using temporary"         → sort butuh tabel sementara (lebih mahal)
-- rows: angka besar dengan LIMIT kecil → scan terlalu banyak baris

-- Sinyal masalah di EXPLAIN PostgreSQL:
-- -> Sort  (cost=...) → ada Sort node → filesort
-- -> Seq Scan         → tidak pakai index sama sekali

-- Target yang baik di EXPLAIN:
-- MySQL: Extra mengandung "Using index" atau "Using index condition",
--        TIDAK mengandung "Using filesort"
-- PostgreSQL: tidak ada Sort node di atas Index Scan

-- ✗ Anti-pattern 1: index hanya untuk filter, lupa sort
CREATE INDEX idx_orders_status ON orders (status);
-- Query: WHERE status = ? ORDER BY created_at DESC LIMIT 20
-- → Filter pakai index, tapi sort masih filesort
-- ✓ Solusi: CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC)

-- ✗ Anti-pattern 2: sort column di depan equality column
CREATE INDEX idx_orders_created_status ON orders (created_at DESC, status);
-- Query: WHERE status = 'paid' ORDER BY created_at DESC
-- → Index tidak optimal karena status bukan prefix
-- ✓ Solusi: (status, created_at DESC) — equality dulu

-- ✗ Anti-pattern 3: mixed ASC/DESC tanpa dukungan database
-- MySQL 5.7: ORDER BY price ASC, created_at DESC
CREATE INDEX idx_price_created ON products (price, created_at);
-- → created_at di index disimpan ASC, tapi query minta DESC
-- → Filesort muncul meski index ada
-- ✓ Solusi: upgrade ke MySQL 8.0+ dan pakai (price ASC, created_at DESC)

-- ✗ Anti-pattern 4: fungsi di ORDER BY
ORDER BY DATE(created_at) DESC  -- fungsi mematikan index sort
-- ✓ Solusi: ORDER BY created_at DESC (gunakan range WHERE jika perlu filter tanggal)

-- ✗ Anti-pattern 5: sort tanpa LIMIT di endpoint publik
SELECT * FROM products WHERE status = 'active' ORDER BY created_at DESC;
-- → Filesort seluruh dataset untuk dikembalikan semua
-- → Tanpa LIMIT: database sort ribuan/jutaan baris
-- ✓ Solusi: selalu tambahkan LIMIT, gunakan pagination