How to Diagnose and Fix Slow SQL Queries and Slow API Calls – Interview Guide

Problem Overview

Performance bottlenecks in backend systems usually manifest as either slow database queries or slow API responses, which degrade user experience and may cause time‑outs.

Core Pain Points

Database layer: missing indexes, full‑table scans, sub‑optimal execution plans, lock contention.

Application layer: deep business‑logic nesting, N+1 queries, lack of caching.

Architecture layer: synchronous blocking calls, long dependency chains, resource saturation.

Diagnosis Process

1. Locate the bottleneck using APM tools (SkyWalking, Zipkin) or logs to see whether the majority of latency resides in DB, cache, or business logic.

2. Determine if it is a SQL issue : if DB time >70% of total latency, treat it as a slow‑SQL problem.

3. Determine if it is an API issue : if DB time is low but overall latency remains high, focus on application code, external services, or resource constraints.

Step‑by‑Step for Slow SQL

Enable slow‑query logging (e.g., SET long_query_time = 1; SET slow_query_log = ON;) and monitor active queries with SHOW PROCESSLIST;.

Run EXPLAIN FORMAT=JSON <SQL> to inspect access type, used index, and estimated rows.

Apply index optimization: create composite indexes covering WHERE, JOIN and ORDER BY columns (e.g.,

ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);

).

Follow the left‑most prefix rule and avoid functions on indexed columns.

Eliminate N+1 queries by batch fetching or using JOIN instead of per‑row subqueries.

Avoid sub‑query nesting; rewrite as joins for better execution plans.

After each change, re‑run EXPLAIN and compare the plan.

Step‑by‑Step for Slow API

Introduce caching (Redis) for read‑heavy data, set appropriate TTL, and guard against cache penetration with empty‑value caching or Bloom filters.

Offload non‑critical tasks (logging, notifications) to asynchronous workers via message queues (Kafka, RocketMQ).

Batch or merge requests to reduce HTTP round‑trips; consider GraphQL for field‑level data selection.

Enable GZIP compression for JSON responses to shrink payload size.

Observability Stack

MySQL Slow Log + pt‑query‑digest for slow‑SQL aggregation.

EXPLAIN / ANALYZE for execution‑plan inspection.

SkyWalking / Zipkin for distributed tracing.

Redis CLI / RedisInsight for cache health.

Prometheus + Grafana for system‑level metrics (CPU, memory, QPS).

Arthas for Java method‑level profiling.

Practical Checklist

Is there a slow SQL? Check APM or logs.

Does the query use an index? Verify type and key in EXPLAIN.

Is there an N+1 pattern? Look for repeated similar queries.

Can the data be cached?

Can non‑critical work be async?

Is pagination or streaming needed?

Architectural Recommendations

Adopt a “prevention‑monitor‑governance” model: enforce SQL reviews, index standards, and CI‑integrated quality checks.

Split monolithic services into lightweight, high‑cohesion micro‑services based on business, performance, and data dimensions.

Build a unified observability platform (logs + metrics + traces) to enable fast root‑cause analysis.

Conclusion

By combining precise monitoring, systematic SQL analysis, application‑level optimizations, and a robust observability framework, teams can turn “slow” symptoms into actionable improvements and embed performance hygiene into the development lifecycle.