Ajie Logo Ajie.
Navigation

© 2026 Ajie Kusumadhany.

Back to Articles

Why Your Database Queries Are Killing Performance Mengapa Query Database Anda Membunuh Performa Aplikasi

Ajie Ajie Kusumadhany
Jun 30, 2026 9 min read
Why Your Database Queries Are Killing Performance Mengapa Query Database Anda Membunuh Performa Aplikasi

You've upgraded your servers. You've added more RAM. You've even switched to a faster framework. But your application still feels sluggish, especially when users start clicking around.

The problem isn't where you think it is.

While you've been optimizing everything else, your database has been quietly drowning under the weight of inefficient queries. And the worst part? Most developers don't realize they're writing performance-killing queries until it's too late.

Let me show you the hidden bottlenecks that are probably slowing down your application right now.

The N+1 Query Problem That Nobody Talks About

Picture this: you need to display a list of 50 blog posts with their authors. Simple, right?

Your ORM fetches the posts with one query. Then, for each post, it fires another query to get the author details. That's 1 + 50 = 51 database queries for a single page load.

This is the infamous N+1 problem, and it's everywhere in production codebases.

Here's what it looks like in practice:

posts = Post.all()
for post in posts:
    print(post.author.name) # Triggers a separate query!

Every time you access post.author, your ORM makes another round trip to the database. With 50 posts, that's 50 unnecessary network calls.

The fix is embarrassingly simple: eager loading.

posts = Post.all().select_related('author') # Django
posts = Post.includes(:author).all # Rails
posts = await Post.findAll({ include: Author }) // Sequelize

Now you're making just 2 queries total: one for posts, one for all authors. Your page load time drops from 500ms to 50ms.

Missing Indexes Are Your Silent Performance Killer

Indexes are like the table of contents in a book. Without them, your database has to scan every single row to find what it's looking for.

Imagine searching for a specific user by email in a table with 2 million records. Without an index, the database reads all 2 million rows. With an index, it jumps directly to the right record.

The difference? 3000ms versus 3ms.

Here's the brutal truth: most developers forget to add indexes on foreign keys, search fields, and frequently filtered columns.

Check your slow query log. You'll probably find queries like this:

SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending';

If user_id and status aren't indexed, this query scans the entire orders table. Every. Single. Time.

Add a composite index and watch the magic happen:

CREATE INDEX idx_user_status ON orders(user_id, status);

But here's the catch: indexes aren't free. They consume disk space and slow down writes (INSERT, UPDATE, DELETE). You need to be strategic.

When to Add Indexes

  • Columns in WHERE clauses that you query frequently
  • Foreign key columns used in JOIN operations
  • Columns used in ORDER BY and GROUP BY
  • Fields that appear in search functionality

When NOT to Add Indexes

  • Tables with very few rows (under 1000)
  • Columns that change constantly
  • Columns with low cardinality (only a few distinct values like boolean fields)

SELECT * Is Your Enemy

Typing SELECT * feels convenient. It's also lazy and wasteful.

When you select all columns, you're forcing the database to retrieve data you don't need. That means more I/O, more memory usage, and more network transfer.

Consider a users table with 30 columns including a large biography text field and a profile_picture blob. You only need the name and email for a dropdown list.

SELECT * FROM users LIMIT 100;

This pulls 100 complete user records with all 30 columns, transferring megabytes of unnecessary data.

SELECT id, name, email FROM users LIMIT 100;

This pulls exactly what you need. The difference in response time can be 10x or more, especially with large text or binary columns.

Be explicit. Your future self will thank you.

The Connection Pool Disaster

Every database connection costs memory and resources. Opening a new connection for every request is expensive—it can take 50-100ms just to establish the connection.

That's why connection pools exist. They maintain a set of reusable database connections that your application can borrow and return.

But here's where developers mess up: they either set the pool too small (causing connection timeout errors under load) or too large (exhausting database resources).

Pool Size Requests/sec Problem
5 50 Connections exhausted under load
20 500 Optimal for most apps
200 400 Database resource exhaustion

The sweet spot is usually between 10-30 connections per application instance, depending on your workload.

Also, always set connection timeouts. A hung connection shouldn't block your entire application.

pool_size=20,
max_overflow=10,
pool_timeout=30,
pool_recycle=3600

Subqueries in Loops Are a Code Smell

This pattern appears in almost every codebase I've audited:

for user_id in user_ids:
    count = db.query("SELECT COUNT(*) FROM orders WHERE user_id = ?", user_id)
    user_order_counts[user_id] = count

If user_ids contains 100 IDs, you just made 100 separate COUNT queries. This is database abuse.

Rewrite it as a single aggregated query:

counts = db.query("""
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    WHERE user_id IN (?, ?, ..., ?)
    GROUP BY user_id
""", *user_ids)

One query. One round trip. Hundreds of milliseconds saved.

Unoptimized JOIN Operations

JOINs are powerful but dangerous. A poorly written JOIN can bring your database to its knees.

The cardinal rule: always JOIN on indexed columns, preferably primary and foreign keys.

SELECT posts.*, users.name
FROM posts
JOIN users ON posts.author_email = users.email;

This JOIN on email addresses (assuming they're not indexed) forces a full table scan. Switch to indexed IDs:

SELECT posts.*, users.name
FROM posts
JOIN users ON posts.author_id = users.id;

Suddenly your query runs 100x faster because it's using indexes for the JOIN condition.

JOIN Order Matters

Most query optimizers are smart, but they're not perfect. Start with the smallest table and JOIN to larger ones.

If you're joining a categories table (50 rows) with a products table (100,000 rows), start with categories.

LIKE Queries With Leading Wildcards

You cannot index a leading wildcard search. It's impossible.

SELECT * FROM products WHERE name LIKE '%phone%';

This query forces a full table scan because the database can't use an index when the wildcard is at the beginning.

If you need full-text search, use proper full-text search features:

  • PostgreSQL: Full-Text Search with tsvector
  • MySQL: FULLTEXT indexes
  • Elasticsearch or Meilisearch for complex search

These tools are built specifically for search and will outperform LIKE queries by orders of magnitude.

Ignoring Query Execution Plans

The EXPLAIN command is your best friend. It shows you exactly how the database executes your query.

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

Look for these red flags in the output:

  • Full Table Scan: The database is reading every row
  • Using Temporary: Creating temporary tables on disk (slow)
  • Using Filesort: Sorting without an index
  • High Row Counts: Examining millions of rows for a simple query

Every database has slightly different EXPLAIN output, but the principles are universal. If you see "Seq Scan" in PostgreSQL or "ALL" in MySQL, you need an index.

Pro Tips for Database Performance

Use database-level caching wisely. Query result caching can dramatically reduce load, but it's not a substitute for good queries. Fix the query first, then add caching.

Monitor slow query logs religiously. Set your slow query threshold to 100ms and review the log weekly. You'll spot patterns immediately.

Batch operations whenever possible. Instead of inserting 1000 records with 1000 INSERT statements, use bulk insert. Most frameworks support this natively.

Use database-specific features. PostgreSQL's partial indexes, MySQL's covering indexes, and MongoDB's aggregation pipelines exist for a reason. Learn them.

Test with production-scale data. A query that runs fine with 100 test records might collapse with 10 million real records. Use realistic datasets in staging.

Consider read replicas for heavy read workloads. If your application reads 95% of the time and writes 5%, split the load across replicas.

Profile before optimizing. Don't guess which queries are slow. Use tools like New Relic, Datadog, or built-in database profilers to measure actual performance.

Key Takeaways

Database performance isn't magic. It's about understanding how databases work and respecting their constraints.

The N+1 problem costs you dozens of unnecessary queries. Missing indexes force full table scans. SELECT * wastes bandwidth. Poor connection management crashes your app under load.

None of these are difficult to fix once you know what to look for.

Start with your slow query log. Find the worst offender. Apply one fix from this article. Measure the improvement.

Then repeat.

Your users won't thank you for optimizing database queries. They'll just stop complaining about how slow your app is.

And sometimes, that's the highest compliment you can get.

Anda sudah upgrade server. Menambah RAM. Bahkan beralih ke framework yang lebih cepat. Tapi aplikasi Anda masih terasa lambat, terutama saat pengguna mulai berinteraksi.

Masalahnya bukan di tempat yang Anda kira.

Sementara Anda sibuk mengoptimalkan hal lain, database Anda diam-diam tenggelam di bawah beban query yang tidak efisien. Dan yang terburuk? Kebanyakan developer tidak sadar mereka menulis query yang membunuh performa sampai terlambat.

Mari saya tunjukkan bottleneck tersembunyi yang mungkin sedang memperlambat aplikasi Anda sekarang.

Masalah N+1 Query Yang Jarang Dibahas

Bayangkan ini: Anda perlu menampilkan daftar 50 artikel blog beserta nama penulisnya. Sederhana, kan?

ORM Anda mengambil artikel dengan satu query. Lalu, untuk setiap artikel, ia menjalankan query lain untuk mendapatkan detail penulis. Itu artinya 1 + 50 = 51 query database untuk satu halaman.

Inilah masalah N+1 yang terkenal, dan ini ada di mana-mana dalam kode produksi.

Begini bentuknya dalam praktik:

posts = Post.all()
for post in posts:
    print(post.author.name) # Memicu query terpisah!

Setiap kali Anda mengakses post.author, ORM Anda membuat perjalanan ke database lagi. Dengan 50 artikel, itu berarti 50 panggilan jaringan yang tidak perlu.

Solusinya sangat sederhana: eager loading.

posts = Post.all().select_related('author') # Django
posts = Post.includes(:author).all # Rails
posts = await Post.findAll({ include: Author }) // Sequelize

Sekarang Anda hanya membuat 2 query total: satu untuk artikel, satu untuk semua penulis. Waktu loading halaman turun dari 500ms ke 50ms.

Index Yang Hilang Adalah Pembunuh Performa Yang Senyap

Index seperti daftar isi dalam buku. Tanpa index, database Anda harus memindai setiap baris untuk menemukan yang dicari.

Bayangkan mencari pengguna spesifik berdasarkan email dalam tabel dengan 2 juta record. Tanpa index, database membaca semua 2 juta baris. Dengan index, ia langsung melompat ke record yang tepat.

Perbedaannya? 3000ms versus 3ms.

Inilah kenyataan pahit: kebanyakan developer lupa menambahkan index pada foreign key, field pencarian, dan kolom yang sering difilter.

Cek slow query log Anda. Anda mungkin akan menemukan query seperti ini:

SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending';

Jika user_id dan status tidak diindex, query ini memindai seluruh tabel orders. Setiap. Kali.

Tambahkan composite index dan saksikan keajaibannya:

CREATE INDEX idx_user_status ON orders(user_id, status);

Tapi ada tangkapannya: index tidak gratis. Index mengonsumsi ruang disk dan memperlambat operasi tulis (INSERT, UPDATE, DELETE). Anda harus strategis.

Kapan Harus Menambahkan Index

  • Kolom dalam klausa WHERE yang sering Anda query
  • Kolom foreign key yang digunakan dalam operasi JOIN
  • Kolom yang digunakan dalam ORDER BY dan GROUP BY
  • Field yang muncul dalam fungsionalitas pencarian

Kapan TIDAK Menambahkan Index

  • Tabel dengan sangat sedikit baris (di bawah 1000)
  • Kolom yang berubah terus-menerus
  • Kolom dengan kardinalitas rendah (hanya beberapa nilai berbeda seperti field boolean)

SELECT * Adalah Musuh Anda

Mengetik SELECT * terasa nyaman. Tapi ini juga malas dan boros.

Ketika Anda select semua kolom, Anda memaksa database mengambil data yang tidak Anda butuhkan. Itu berarti lebih banyak I/O, lebih banyak penggunaan memori, dan lebih banyak transfer jaringan.

Pertimbangkan tabel users dengan 30 kolom termasuk field teks biography yang besar dan blob profile_picture. Anda hanya butuh nama dan email untuk daftar dropdown.

SELECT * FROM users LIMIT 100;

Ini menarik 100 record user lengkap dengan semua 30 kolom, mentransfer megabyte data yang tidak perlu.

SELECT id, name, email FROM users LIMIT 100;

Ini menarik persis yang Anda butuhkan. Perbedaan waktu respons bisa 10x atau lebih, terutama dengan kolom teks besar atau biner.

Jadilah eksplisit. Diri Anda di masa depan akan berterima kasih.

Bencana Connection Pool

Setiap koneksi database membutuhkan memori dan resource. Membuka koneksi baru untuk setiap request itu mahal—bisa butuh 50-100ms hanya untuk membuat koneksi.

Itulah mengapa connection pool ada. Mereka memelihara set koneksi database yang dapat digunakan kembali yang aplikasi Anda bisa pinjam dan kembalikan.

Tapi di sinilah developer melakukan kesalahan: mereka membuat pool terlalu kecil (menyebabkan error timeout koneksi di bawah beban) atau terlalu besar (menghabiskan resource database).

Ukuran Pool Request/detik Masalah
5 50 Koneksi habis saat beban tinggi
20 500 Optimal untuk kebanyakan aplikasi
200 400 Resource database habis

Titik manis biasanya antara 10-30 koneksi per instance aplikasi, tergantung beban kerja Anda.

Juga, selalu set timeout koneksi. Koneksi yang tergantung tidak boleh memblokir seluruh aplikasi Anda.

pool_size=20,
max_overflow=10,
pool_timeout=30,
pool_recycle=3600

Subquery Dalam Loop Adalah Code Smell

Pola ini muncul di hampir setiap codebase yang pernah saya audit:

for user_id in user_ids:
    count = db.query("SELECT COUNT(*) FROM orders WHERE user_id = ?", user_id)
    user_order_counts[user_id] = count

Jika user_ids berisi 100 ID, Anda baru saja membuat 100 query COUNT terpisah. Ini adalah penyalahgunaan database.

Tulis ulang sebagai satu query agregat:

counts = db.query("""
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    WHERE user_id IN (?, ?, ..., ?)
    GROUP BY user_id
""", *user_ids)

Satu query. Satu perjalanan. Ratusan milidetik terhemat.

Operasi JOIN Yang Tidak Dioptimalkan

JOIN itu powerful tapi berbahaya. JOIN yang ditulis dengan buruk bisa membuat database Anda lumpuh.

Aturan utama: selalu JOIN pada kolom yang diindex, sebaiknya primary dan foreign key.

SELECT posts.*, users.name
FROM posts
JOIN users ON posts.author_email = users.email;

JOIN pada alamat email ini (dengan asumsi tidak diindex) memaksa full table scan. Ganti ke ID yang diindex:

SELECT posts.*, users.name
FROM posts
JOIN users ON posts.author_id = users.id;

Tiba-tiba query Anda berjalan 100x lebih cepat karena menggunakan index untuk kondisi JOIN.

Urutan JOIN Itu Penting

Kebanyakan query optimizer pintar, tapi mereka tidak sempurna. Mulai dengan tabel terkecil dan JOIN ke yang lebih besar.

Jika Anda join tabel categories (50 baris) dengan tabel products (100.000 baris), mulai dengan categories.

Query LIKE Dengan Wildcard Di Awal

Anda tidak bisa mengindex pencarian wildcard di awal. Ini mustahil.

SELECT * FROM products WHERE name LIKE '%phone%';

Query ini memaksa full table scan karena database tidak bisa menggunakan index ketika wildcard ada di awal.

Jika Anda butuh full-text search, gunakan fitur full-text search yang tepat:

  • PostgreSQL: Full-Text Search dengan tsvector
  • MySQL: FULLTEXT indexes
  • Elasticsearch atau Meilisearch untuk pencarian kompleks

Tool-tool ini dibangun khusus untuk pencarian dan akan mengungguli query LIKE dengan margin yang sangat besar.

Mengabaikan Query Execution Plan

Perintah EXPLAIN adalah sahabat terbaik Anda. Ia menunjukkan persis bagaimana database menjalankan query Anda.

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

Cari tanda-tanda bahaya ini dalam output:

  • Full Table Scan: Database membaca setiap baris
  • Using Temporary: Membuat tabel sementara di disk (lambat)
  • Using Filesort: Sorting tanpa index
  • Jumlah Row Tinggi: Memeriksa jutaan baris untuk query sederhana

Setiap database memiliki output EXPLAIN yang sedikit berbeda, tapi prinsipnya universal. Jika Anda melihat "Seq Scan" di PostgreSQL atau "ALL" di MySQL, Anda butuh index.

Tips Praktis untuk Performa Database

Gunakan caching level database dengan bijak. Query result caching bisa mengurangi beban secara dramatis, tapi ini bukan pengganti query yang baik. Perbaiki query dulu, baru tambah caching.

Monitor slow query log secara religius. Set threshold slow query ke 100ms dan review log setiap minggu. Anda akan langsung melihat pola.

Batch operasi kapanpun memungkinkan. Alih-alih insert 1000 record dengan 1000 statement INSERT, gunakan bulk insert. Kebanyakan framework mendukung ini secara native.

Gunakan fitur spesifik database. Partial index PostgreSQL, covering index MySQL, dan aggregation pipeline MongoDB ada untuk alasan tertentu. Pelajari mereka.

Test dengan data skala produksi. Query yang berjalan baik dengan 100 record test mungkin collapse dengan 10 juta record nyata. Gunakan dataset realistis di staging.

Pertimbangkan read replica untuk beban baca yang berat. Jika aplikasi Anda membaca 95% waktu dan menulis 5%, bagi beban di replica.

Profile sebelum optimasi. Jangan tebak query mana yang lambat. Gunakan tools seperti New Relic, Datadog, atau profiler database built-in untuk mengukur performa aktual.

Kesimpulan Utama

Performa database bukan sulap. Ini tentang memahami bagaimana database bekerja dan menghormati keterbatasan mereka.

Masalah N+1 memakan puluhan query yang tidak perlu. Index yang hilang memaksa full table scan. SELECT * membuang bandwidth. Manajemen koneksi yang buruk membuat aplikasi Anda crash saat beban tinggi.

Semua ini tidak sulit diperbaiki begitu Anda tahu apa yang harus dicari.

Mulai dengan slow query log Anda. Temukan pelanggar terburuk. Terapkan satu perbaikan dari artikel ini. Ukur peningkatannya.

Lalu ulangi.

Pengguna Anda tidak akan berterima kasih karena mengoptimalkan query database. Mereka hanya akan berhenti mengeluh tentang betapa lambatnya aplikasi Anda.

Dan kadang, itulah pujian tertinggi yang bisa Anda dapatkan.

#Database #Performance #Backend #SQL