Bagaimana cara mengoptimalkan kueri SQL yang lambat?

Question

Accepted Answer

Mengoptimalkan kueri lambat dimulai dengan **menganalisis rencana eksekusi** (`EXPLAIN`) untuk menemukan bottleneck, kemudian menerapkan perbaikan yang ditargetkan — paling umum **menambah indeks**, menulis ulang kueri, dan menghindari anti-pattern umum. Keuntungan terbesar biasanya adalah mengubah full table scan menjadi index lookup.

## Langkah 1: analisis dengan EXPLAIN

```sql
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 5;
-- shows the EXECUTION PLAN: scan type (Seq Scan = full scan, Index Scan = using an index),
-- estimated/actual rows, cost, and timing → reveals WHERE the time goes
```

`EXPLAIN` (atau `EXPLAIN ANALYZE`) menunjukkan bagaimana database mengeksekusi kueri — yang krusial, apakah melakukan **full table scan** (lambat) atau menggunakan **indeks** (cepat). Selalu profile sebelum mengoptimalkan.

## Keuntungan terbesar: tambah indeks

```sql
-- if EXPLAIN shows a Seq Scan on a filtered/joined column → add an index
CREATE INDEX idx_orders_customer ON orders(customer_id);
-- turns a full scan into a fast index lookup
```

Perbaikan yang paling umum dan berdampak: **tambah indeks** pada kolom yang digunakan dalam `WHERE`, `JOIN`, dan `ORDER BY`. Ini mengubah full table scan menjadi fast lookup.

## Anti-pattern umum yang harus diperbaiki

```sql
-- ❌ functions on a column prevent index use
WHERE UPPER(email) = 'X@Y.COM'        -- can't use an index on email
WHERE email = 'x@y.com'               -- ✅ can use the index

-- ❌ leading wildcard LIKE can't use an index
WHERE name LIKE '%smith'              -- ✅ 'smith%' (prefix) can use an index

-- ❌ SELECT * fetches unneeded columns
SELECT * → SELECT only_needed_columns  -- less data, may use a covering index

-- ❌ N+1 queries (querying in a loop) → use a single JOIN instead
```

## Teknik optimasi lainnya

```text
✓ Fix N+1 queries (one query per row) → use JOINs / batch into one query
✓ Add appropriate indexes (including COMPOSITE for multi-column filters)
✓ Reduce data scanned — filter early, select only needed columns, paginate
✓ Rewrite subqueries as JOINs where more efficient
✓ Use EXISTS instead of IN for existence checks on large subqueries
✓ Keep statistics updated (ANALYZE) so the planner makes good choices
✓ Consider denormalization / materialized views for expensive repeated queries
✓ Watch for missing indexes on FOREIGN KEYS (joins scan without them)
```

## Mengapa ini penting

Optimasi kueri adalah pengetahuan penting untuk performa database — kueri lambat adalah salah satu penyebab utama performa aplikasi yang buruk, dan memahami cara menemukan dan memperbaikinya adalah skill level senior yang penting bagi siapa saja yang bekerja dengan database dalam skala besar.

Disiplin fundamental adalah **analisis dengan `EXPLAIN`** (memeriksa rencana eksekusi untuk menemukan bottleneck aktual — krusial apakah kueri melakukan slow full table scan atau fast index lookup) sebelum mengoptimalkan, daripada menebak — pendekatan berbasis pengukuran ini yang membuat optimasi efektif.

**Keuntungan tunggal terbesar dan paling umum adalah menambah indeks** pada kolom yang difilter, dijoined, dan disortir (mengubah full table scan menjadi fast lookup — sering meningkatkan waktu kueri berkali-kali lipat), menjadikan pengetahuan indeks sentral untuk optimasi.

Memahami **anti-pattern umum** sama pentingnya: fungsi pada kolom dan leading-wildcard `LIKE` mencegah penggunaan indeks, `SELECT *` mengambil data yang tidak perlu, dan khususnya **kueri N+1** (query dalam loop daripada single JOIN — pembunuh performa yang sering dan berat dalam kode aplikasi) — mengenali dan memperbaiki ini mengatasi penyebab paling umum dari kueri lambat.

Teknik lain (composite index, mengurangi data yang dipindai, menulis ulang subquery sebagai JOIN, menjaga statistik tetap terbaru, materialized view untuk kueri yang mahal) melengkapi toolkit.

Karena performa kueri langsung mempengaruhi responsivitas dan skalabilitas aplikasi, dan karena pendekatan sistematis (analisis dengan EXPLAIN, kemudian perbaiki — biasanya dengan menambah indeks dan menghilangkan anti-pattern seperti N+1) adalah yang membuat database tetap cepat di bawah beban, menguasai optimasi kueri adalah pengetahuan senior yang penting dan bernilai tinggi yang membedakan developer yang dapat membangun aplikasi database-backed yang performan dan scalable — skill yang sering relevan, penting, dan topik interview umum untuk performa database.