Backend interview prep

Start

Slow Queries Interview Questions

Master SQL Slow Query Analysis with interview-focused questions covering slow query logs, blocking queries, deadlocks, N+1 problems, missing indexes, query profiling, root cause analysis, and production troubleshooting.

Introduction

One of the most common production issues in enterprise applications is slow database queries.

A single poorly written SQL query can

  • Increase CPU utilization
  • Consume excessive memory
  • Block other transactions
  • Cause connection pool exhaustion
  • Slow the entire application

Production engineers spend a significant amount of time identifying and optimizing slow queries.

Understanding how to diagnose and resolve slow SQL is an essential interview topic for

  • Backend Developers
  • Database Engineers
  • DevOps Engineers
  • SREs
  • Solution Architects

Slow Query Troubleshooting Architecture

flowchart LR

Application --> SlowQuery --> SlowQueryLog --> ExecutionPlan --> RootCause --> Optimization --> FastQuery

1. What is a Slow Query?

Answer

A Slow Query is a SQL statement that takes significantly longer than expected to execute.

For example

Expected

20 ms

Actual

8 seconds

2. What causes Slow Queries?

Common reasons

  • Missing Indexes
  • Full Table Scans
  • Poor JOINs
  • Large Result Sets
  • Blocking
  • Deadlocks
  • Bad Query Design
  • Network Latency
  • Disk IO

3. How do you identify Slow Queries?

Common techniques

  • Slow Query Log
  • Database Monitoring
  • APM Tools
  • Query Profilers
  • EXPLAIN
  • EXPLAIN ANALYZE

Troubleshooting Flow

flowchart LR

SlowQuery --> CollectMetrics --> ExplainPlan --> RootCause --> Optimization

4. What is the Slow Query Log?

The Slow Query Log records queries exceeding a configured execution time.

Example

MySQL

SET GLOBAL slow_query_log = ON;

5. What information does the Slow Query Log provide?

Typically includes

  • SQL Statement
  • Execution Time
  • Lock Time
  • Rows Examined
  • Rows Returned
  • Timestamp
  • User

6. What is EXPLAIN?

EXPLAIN shows

  • Execution Plan
  • Index Usage
  • Join Order
  • Estimated Cost

Useful for optimization.

7. What is EXPLAIN ANALYZE?

Executes the query

and displays

actual execution statistics.

Useful for

production troubleshooting.

8. What is a Blocking Query?

A query waiting because another transaction holds a lock.

Example

Transaction A

locks row

↓

Transaction B

waits

Blocking

flowchart LR

TransactionA --> Lock

TransactionB --> Waiting

9. What is Lock Contention?

Multiple transactions compete

for the same data.

Results

  • Waiting
  • Lower Throughput
  • Increased Response Time

10. What is a Deadlock?

Two transactions

wait for each other.

Example

Transaction A

↓

Row1

↓

Needs Row2

Transaction B

↓

Row2

↓

Needs Row1

Deadlock

flowchart LR

TxnA --> Row1

Row1 --> TxnB

TxnB --> Row2

Row2 --> TxnA

11. What causes Deadlocks?

  • Different update order
  • Long transactions
  • Missing indexes
  • Large updates
  • Multiple row locks

12. What is a Missing Index?

Without an index

Database scans

every row.

Example

SELECT *

FROM Orders

WHERE customer_id=100;

No index

↓

Full Table Scan.

13. Why are Full Table Scans slow?

Database reads

every row

instead of

only matching rows.

14. What is a Large Result Set?

Returning

millions of rows

even when

only a few are needed.

15. Why should SELECT * be avoided?

Problems

  • More Network Traffic
  • More Memory
  • More Disk IO
  • Poor Cache Usage

Better

SELECT

id,

name

16. What is the N+1 Query Problem?

Application executes

1 Query

+

1000 Queries

instead of

one JOIN.

Very common in ORMs.

N+1 Problem

flowchart LR

Application --> 1Query

1Query --> 1000Queries

1000Queries --> Database

17. How do you solve the N+1 Query Problem?

Use

  • JOIN FETCH
  • Batch Fetching
  • Entity Graph
  • DTO Projections

instead of repeated queries.

18. What is Parameter Sniffing?

Some databases optimize execution plans based on the first parameter value.

A cached plan may perform poorly for different values.

(Common in SQL Server; concept is useful to understand.)

19. What is Query Profiling?

Measuring

  • CPU
  • Memory
  • IO
  • Execution Time

for SQL statements.

20. What is Root Cause Analysis (RCA)?

Finding

the real reason

behind

slow performance.

Avoid fixing only symptoms.

RCA Process

flowchart LR

Incident --> Evidence --> RootCause --> Fix --> Validation

21. What production metrics should be checked?

  • CPU
  • Memory
  • Disk IO
  • Active Sessions
  • Lock Waits
  • Deadlocks
  • Query Latency
  • Replication Lag
  • Buffer Cache Hit Ratio

22. How do indexes help?

Indexes reduce

  • Rows Scanned
  • Disk Reads
  • CPU Usage

leading to

faster queries.

23. What is Query Rewriting?

Changing SQL

without changing results.

Example

Bad

SELECT *

Better

SELECT

employee_id,

name

24. What is Batch Processing?

Instead of

1 Million Updates

Use

1000 Rows

per Batch

Benefits

  • Lower Locks
  • Better Throughput

25. Banking Example

Slow Query

Transactions

↓

Table Scan

↓

15 Seconds

After index

20 ms

26. E-Commerce Example

Customer search

Before

SELECT *

After

Compound Index

↓

Milliseconds.

27. HR Example

Employee lookup

using

Primary Key

↓

Instant response.

28. Logging Example

Partition logs

by month

↓

Read one partition

↓

Lower IO.

29. Production Incident Example

Symptoms

  • Application timeout
  • High CPU
  • Slow database

Investigation

  • Slow Query Log
  • EXPLAIN
  • Missing Index

Fix

Create index

↓

Response time reduced.

30. Common Slow Query Mistakes

  • Missing indexes
  • SELECT *
  • Functions on indexed columns
  • Large OFFSET pagination
  • Correlated subqueries
  • Cartesian joins
  • Unnecessary sorting
  • Returning too many rows

Slow Query Investigation Workflow

flowchart LR

Alert --> SlowQueryLog --> Explain --> AnalyzeIndexes --> OptimizeSQL --> Deploy --> Monitor

Enterprise Best Practices

  • Enable Slow Query Log.
  • Monitor execution times continuously.
  • Use EXPLAIN before deploying new SQL.
  • Keep statistics updated.
  • Create indexes based on query patterns.
  • Avoid long-running transactions.
  • Optimize JOIN order.
  • Return only required columns.
  • Review application-generated SQL.
  • Continuously monitor production metrics.

Quick Revision

Topic Key Point
Slow Query Long Execution Time
Slow Query Log Detect Slow SQL
EXPLAIN Execution Plan
EXPLAIN ANALYZE Actual Runtime
Blocking Waiting Transaction
Deadlock Circular Waiting
Missing Index Full Table Scan
N+1 Problem Excessive Queries
Query Profiling Performance Analysis
RCA Root Cause Analysis

Interview Tips

Interviewers frequently ask

  • What is a Slow Query?
  • How do you identify slow SQL?
  • What is the Slow Query Log?
  • Explain Blocking.
  • Explain Deadlocks.
  • What is the N+1 Query Problem?
  • Why is SELECT * bad?
  • How do you troubleshoot a slow production database?
  • Give a real production optimization example.
  • What metrics do you monitor?

A strong production troubleshooting approach is:

  1. Receive an alert from monitoring.
  2. Identify slow SQL using the Slow Query Log or APM.
  3. Generate the execution plan.
  4. Check index usage and join strategy.
  5. Identify blocking or deadlocks.
  6. Optimize SQL or indexes.
  7. Validate improvements.
  8. Continue monitoring after deployment.

This demonstrates real-world production troubleshooting experience.

Summary

Slow query analysis is one of the most valuable production database skills. By combining Slow Query Logs, Execution Plans, Index Optimization, Query Rewriting, Root Cause Analysis, and continuous monitoring, engineers can dramatically improve application performance.

Mastering slow query troubleshooting prepares Backend Developers, Database Engineers, DevOps Engineers, SREs, and Solution Architects to diagnose and resolve enterprise-scale database performance problems efficiently.