How to Improve Query Performance in Databases?

Scenario Based Interview Question

If you are not a paid member of Medium, please use my friend link to read the entire article: Friend Link

You know that moment when you hit Run on a seemingly harmless SQL query… and suddenly, time slows down?

You start staring at the spinning cursor, wondering if your database has joined the witness protection program, if your code secretly hates you, or if you’ve made some existential mistake in life.

At first, it’s fine. Your database is small, the tables are neat, queries fly like they should. But fast forward a few months — millions of rows, joins stacking on joins, aggregations chewing through everything — and suddenly, your once-snappy query is a snail.

Worse, the API that depends on it starts timing out, users get frustrated, and you realize this isn’t just about a slow query… it’s a ticking time bomb for your system.

Let’s peel back the curtain and see what’s happening under the hood.

We’ll explore why queries slow down, what the database engine is really thinking, and — most importantly — how to fix them step by step. No guesswork, no random tweaks. Just methodical, developer-friendly problem-solving.

Why Query Performance Optimization Matters:

Databases are the backbone of most applications and when queries are inefficient, it impacts:

• User Experience: Users expect instant results. A slow query means a slow app.
• Scalability: As your data grows, even a slightly inefficient query can exponentially degrade.
• Infrastructure Costs: Slow queries consume CPU, memory, and I/O. On cloud databases, that’s direct money lost.
• System Stability: In microservices, one slow query can hold connections open and trigger cascading timeouts.

Optimizing queries is not an optional skill — it’s essential for building scalable, reliable systems.

What Slows Queries Down?

Before we jump into optimization, let’s understand what actually makes queries slow in the first place.

When performance drops, it’s rarely because of a single cause — it’s usually a mix of design choices, indexing issues, or outdated statistics quietly piling up over time.

Here are some of the most common culprits:

1. Missing Indexes:

Without proper indexes, the database performs a full table scan — reading every row to find what you’re looking for.

Imagine flipping through all 500 pages of a book just to find one word — that’s exactly what your database does without an index.

-- This query scans the entire table if 'customer_name' isn’t indexed
SELECT * 
FROM customers 
WHERE customer_name = 'John Doe';

Fix: Create an index on the column used in the WHERE clause to let the database jump directly to the relevant records.

CREATE INDEX idx_customer_name ON customers(customer_name);

2. Poorly Written Joins:

Joins are incredibly powerful, but they can also become performance nightmares when:

• You’re joining large datasets without proper indexes.
• You’re joining on non-key or non-indexed columns.

-- Expensive join without indexes
SELECT c.customer_name, o.order_id
FROM customers c
JOIN orders o ON c.customer_name = o.customer_name;

Fix: Always join using indexed or primary key columns.

-- Better: Join using indexed columns
SELECT c.customer_name, o.order_id
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

3. Unnecessary Columns:

That innocent-looking SELECT *? It’s often the silent killer. It fetches every column — even those you don’t need — increasing I/O, memory, and network load.

-- Avoid this
SELECT * FROM orders WHERE status = 'PENDING';

Fix: Fetch only what you need.

SELECT order_id, order_date, amount
FROM orders
WHERE status = 'PENDING';

4. Functions on Indexed Columns:

When you wrap an indexed column inside a function, the index becomes useless — the database has to compute the value for every row first.

-- Index on 'name' won’t be used
SELECT * FROM employees WHERE UPPER(name) = 'JOHN';

Fix: Either store data in a consistent case or use a function-based index.

-- Function-based index example
CREATE INDEX idx_upper_name ON employees(UPPER(name));

5. Nested Subqueries:

Subqueries that run once per row are performance traps. They might look elegant but can multiply execution time dramatically.

-- Slow: executes inner query for each employee
SELECT e.employee_name,
       (SELECT department_name 
        FROM departments d 
        WHERE d.dept_id = e.dept_id) AS dept_name
FROM employees e;

Fix: Use a proper join instead.

-- Faster: single join replaces repeated subquery calls
SELECT e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

6. Outdated Statistics:

The database optimizer relies on internal statistics — table size, row count, data distribution, and density — to decide the best execution plan.

When these stats are outdated, the optimizer can make poor choices.

Fix: Keep statistics fresh.

-- Oracle
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');
-- PostgreSQL
ANALYZE;
-- MySQL
ANALYZE TABLE customers;

7. Over-Indexing:

Yes, too many indexes can hurt too. Every INSERT, UPDATE, or DELETE must update all relevant indexes, slowing down write operations.

Fix: Index only what’s queried frequently. Avoid indexing columns that rarely appear in filters or joins.

8. Unintentional Cartesian Products:

A Cartesian product occurs when you join two tables without a proper ON condition, causing every row in one table to join with every row in the other. For example:

SELECT c.name, o.order_id
FROM customers c, orders o;

If there are 10,000 customers and 5,000 orders, this query returns 50 million rows — even if they’re unrelated.

Optimization Tip: Always use explicit joins with clear conditions on indexed columns:

SELECT c.name, o.order_id
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

You can also add filters (WHERE clause) and limit clauses to further reduce the dataset processed.

Step-by-Step Techniques to Improve Query Performance:

Once you know what’s slowing your queries down, it’s time to fix them — step by step.

Let’s go from the simplest wins to more advanced optimizations.

Step 1: Analyze Before You Optimize

The golden rule — never guess. Always measure.

Every major RDBMS provides a way to peek under the hood using **EXPLAIN or `EXPLAIN ANALYZE`**. This shows you the execution plan: how the query runs, which indexes (if any) it uses, and where the bottlenecks lie.

EXPLAIN ANALYZE
SELECT * 
FROM orders 
WHERE customer_id = 1001;

Output reveals:

• Whether it used an index or a sequential scan
• How much time each step took
• Estimated vs. actual rows processed

You can’t optimize what you don’t understand — and EXPLAIN is where all tuning begins.

Step 2: Optimize Index Usage

Indexes are the foundation of fast queries — they act like a table of contents for your database.

1. Create Indexes on Filtering Columns:

If you frequently filter or join by a column, index it.

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

This lets the database jump directly to relevant rows instead of scanning the entire table.

2. Use Composite Indexes:

If you often filter by multiple columns, composite (multi-column) indexes help.

CREATE INDEX idx_orders_customer_date 
ON orders(customer_id, order_date);

The column order matters. The index above is only useful if your query filters by customer_id first.

3. Avoid Redundant Indexes:

Too many similar indexes waste space and slow down writes.

-- Redundant
CREATE INDEX idx_customer_id ON orders(customer_id);
CREATE INDEX idx_customer_id_date ON orders(customer_id, order_date);

In this case, idx_customer_id is unnecessary — the second one already covers it.

Step 3: Avoid SELECT *

SELECT * is one of those things that seems harmless… until your data grows.

Fetching all columns increases:

• Disk I/O
• Memory usage
• Network latency

-- Bad
SELECT * FROM users WHERE status = 'ACTIVE';

Fetch only what you need:

SELECT id, name, email 
FROM users 
WHERE status = 'ACTIVE';

A small change that can make a huge difference — especially on high-traffic endpoints.

Step 4: Optimize Joins

Joins are often the main reason queries slow down — especially on large datasets.

1. Ensure Join Columns Are Indexed:

SELECT o.id, o.amount, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'India';

Both orders.customer_id and customers.id should be indexed to avoid full scans.

2. Filter Before Joining:

Reduce data early. Always filter first, join later.

-- Bad: joins entire tables, then filters
SELECT * 
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'India';

-- Better: filter first, then join smaller set
SELECT o.id, o.amount, c.name
FROM (SELECT id, name FROM customers WHERE country = 'India') c
JOIN orders o ON o.customer_id = c.id;

Step 5: Optimize Subqueries

Subqueries can make code elegant but slow if misused.

Replace IN with EXISTS:

EXISTS stops after finding the first match, while IN often evaluates all possible rows.

-- Bad
SELECT * 
FROM users 
WHERE id IN (SELECT user_id FROM orders);

-- Better
SELECT * 
FROM users u
WHERE EXISTS (
  SELECT 1 
  FROM orders o 
  WHERE o.user_id = u.id
);

Step 6: Partition Large Tables

When a table crosses tens of millions of rows, even the best indexes start sweating.

Partitioning helps by splitting large tables into smaller, more manageable chunks — usually by date or region.

-- Example (PostgreSQL)
CREATE TABLE orders_2024 PARTITION OF orders 
FOR VALUES FROM ('2024-01-01') TO ('2024-12-31');

Now, a query like:

SELECT * 
FROM orders 
WHERE order_date >= '2024-10-01';

will only scan the relevant partition, not the entire dataset.

Step 7: Cache Repeated Queries

If your app executes the same query repeatedly (like a dashboard or API), caching can work wonders.

Application-Level Cache:

Use Redis or Memcached to temporarily store results.

// Java pseudo-code
String cacheKey = "top_customers";
if (redis.exists(cacheKey)) {
    return redis.get(cacheKey);
} else {
    String result = runQuery();
    redis.set(cacheKey, result, EXPIRY_TIME);
    return result;
}

Database-Level Cache:

Some databases support caching via materialized views.

CREATE MATERIALIZED VIEW top_customers AS
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

-- Refresh periodically
REFRESH MATERIALIZED VIEW top_customers;

Step 8: Keep Statistics Updated

Optimizers rely heavily on table statistics — row count, distribution, and density — to choose the best plan. Outdated stats can make even a good query plan go bad.

-- PostgreSQL
ANALYZE;

-- MySQL
ANALYZE TABLE orders;

Enable automatic stats collection (like Postgres autovacuum) in production environments.

Step 9: Denormalize (When It Makes Sense)

Normalization avoids redundancy, but too many joins can kill performance for read-heavy workloads.

Denormalization trades a bit of storage for faster reads.

-- Instead of joining customers every time
-- Store customer_name directly in orders
ALTER TABLE orders ADD COLUMN customer_name VARCHAR(255);

UPDATE orders o
SET customer_name = c.name
FROM customers c
WHERE o.customer_id = c.id;

For analytics or dashboards, this can be game-changing.

Step 10: Monitor Continuously:

Optimization isn’t a one-time effort. As data grows, queries evolve — and slow down again.

Use monitoring tools to stay ahead:

• PostgreSQL: pg_stat_statements
• MySQL: Performance Schema
• SQL Server: Query Store / Profiler
• App-level: New Relic, Datadog, Prometheus

Track:

• Query latency over time
• Changes in execution plans
• CPU, I/O, and memory usage

The key is consistency — measure, optimize, monitor, repeat.

Step 11: Consider Query Plan Caching

When similar queries run repeatedly (like API calls with different parameters), databases can reuse compiled execution plans instead of re-optimizing each time.

In PostgreSQL and SQL Server, this happens automatically.

In MySQL, you can use prepared statements:

PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
SET @user_id = 101;
EXECUTE stmt USING @user_id;

This reduces the overhead of query parsing and optimization for repeated executions.

Step 12: Use Connection Pooling

Opening and closing connections repeatedly is expensive. Use connection pools to reuse existing connections efficiently.

Example (Java with HikariCP):

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");
config.setUsername("dbuser");
config.setPassword("secret");
config.setMaximumPoolSize(10);

HikariDataSource ds = new HikariDataSource(config);
Connection conn = ds.getConnection();

Connection pooling minimizes overhead and improves response time for high-traffic applications.

Example: A Real-World Query Optimization Journey

Let’s walk through how you’d actually tune a slow query — step by step.

The Problem:

You’re working on an e-commerce analytics dashboard. One of the endpoints keeps timing out whenever someone filters orders by country.

Here’s the culprit query:

SELECT * 
FROM orders 
WHERE customer_id IN (
    SELECT id 
    FROM customers 
    WHERE country = 'India'
);

At first glance, it looks fine — but it’s painfully slow.

Symptoms:

• The API endpoint takes 2–3 seconds to respond.
• CPU utilization spikes during execution.
• The database log shows sequential scans on both tables.

Time to fix it systematically — not by guesswork.

Step 1: Analyze Before You Optimize

We start with:

EXPLAIN ANALYZE
SELECT * 
FROM orders 
WHERE customer_id IN (
    SELECT id FROM customers WHERE country = 'India'
);

The plan reveals two red flags:

• A Nested Loop Subquery running for every row in orders
• Sequential scans on both orders and customers

This tells us the database is literally scanning the entire orders table and then, for each row, scanning customers. That’s a recipe for disaster when you have millions of records.

Step 2: Add Indexes

We identify key filtering and join columns — orders.customer_id and customers.country.

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_customers_country ON customers(country);

Now, the database has shortcuts to locate relevant rows faster.

Step 3: Rewrite the Query Using JOIN

Nested subqueries (IN clauses) are often slower because they can’t fully utilize indexes. Let’s rewrite it with a JOIN — it’s more readable and more efficient.

SELECT 
    o.id, 
    o.amount, 
    o.created_at, 
    c.name
FROM orders o
JOIN customers c 
    ON o.customer_id = c.id
WHERE c.country = 'India';

Now, the optimizer can use index joins instead of scanning both tables independently.

Step 4: Verify Execution Plan

Let’s validate with EXPLAIN ANALYZE again:

EXPLAIN ANALYZE
SELECT 
    o.id, 
    o.amount, 
    o.created_at, 
    c.name
FROM orders o
JOIN customers c 
    ON o.customer_id = c.id
WHERE c.country = 'India';

This time, the plan shows:

• Index Scan on customers(country)
• Nested Loop Join leveraging orders.customer_id index
• Reduced estimated rows from hundreds of thousands to just a few thousand

Step 5: Measure the Results

Execution time drops from ~2 seconds to under 150 ms.

That’s a >10x improvement, achieved with just indexing and query rewriting.

But we’re not done — we could still take it further by caching frequent queries, updating statistics, and using connection pooling if this endpoint is hit often.

Takeaway:

Performance optimization isn’t about tweaking random settings — it’s a process:

1. Measure (EXPLAIN ANALYZE)
2. Identify the root cause
3. Apply targeted improvements
4. Validate and iterate

When you understand how the database thinks, you stop guessing and start engineering.

Final Thoughts:

Query performance isn’t about magic tricks — it’s about understanding how databases think. Every optimization step is a conversation with the database, helping it make smarter choices.

If you take away just one thing, let it be this:

Don’t treat performance optimization as a one-time activity. Treat it as an ongoing discipline.

If you or someone you know recently had an interview or if you’d like me to explain any topic, feel free to reach out to me via email. I’ll write an detailed article on the same.

If you need help with interview preparation, or need consultation in general. Please reach out to me over the email.

Email: shivamsrivastava.iec@gmail.com

For collaboration, clarifications or support please connect with me on:

Email: shivamsrivastava.iec@gmail.com

Quora: Shivam Srivastava

X.com (Twitter): Shivam on X

Buy Me a Coffee: Shivam Srivastava

If you liked this article, you’ll also enjoy my below list of articles:

[embed]Interview Experiences and Learnings Edit descriptionmedium.com

[embed]Deep Dive Series Edit descriptionmedium.com