API is slow because of SQL

Hook: When an API is slow, SQL is often the first place I check because one bad query can turn a 50 ms request into a 5 second wait.

Question: How do you debug and fix an API that is slow because of SQL?

Answer: I first prove the database is the bottleneck by checking request timing, slow query logs, and EXPLAIN ANALYZE. Then I look for common causes like missing indexes, scanning too many rows, N+1 queries, bad joins, or lock waits, and I fix the smallest root cause, not just the symptom.

Interview-Ready Answer: I would trace the API request, identify the slow SQL, and run EXPLAIN ANALYZE to see whether the time is spent on scans, sorts, joins, or locks. If the query is reading too many rows, I would add the right index, rewrite the filter to be sargable, or batch the work to avoid N+1 queries. Then I would re-check p95 latency and confirm the plan actually changed, because indexes help only when the query shape matches them.

What is really happening?

An API is not slow just because the database exists; it is slow because the request is waiting somewhere in the SQL path. That wait can be from query execution, a lock wait, a slow connection checkout, or even moving a large result set over the network. p95 means the 95th percentile: 95% of requests are faster than that number, so it is a great way to spot real user pain.

How to debug it, step by step

1. Confirm the delay is in SQL. Check APM traces or app logs and separate total request time from DB time. If the API spends 2.8s and the SQL span spends 2.6s, you already know where to look.
2. Find the exact query. Use slow query logs, query fingerprints, or request tracing. Do not guess from the endpoint name; one endpoint often calls several SQL statements.
3. Inspect the plan. Run EXPLAIN ANALYZE to compare the planned path with the actual work. EXPLAIN shows the plan, while ANALYZE executes it and reports real rows, loops, and timing.
4. Look for the classic SQL mistakes. A sequential scan (reading the whole table) on a 10 million row table, a sort over too many rows, a join that multiplies rows, or a function on a filtered column can all make a query much slower than expected.
5. Fix the shape of the query. Add an index that matches the WHERE and ORDER BY columns, rewrite filters so they are sargable (written in a way the database can use an index), batch small queries into one, and return only the columns you need.
6. Verify the win. Re-run the query and compare p95 latency, rows scanned, and buffer reads. A good fix is measurable, not just intuitive.

Common patterns and what they usually mean

Performance notes interviewers like

• A full table scan is usually O(n) because the database checks many rows one by one.
• A B-tree index lookup is usually about O(log n) for the search, plus the cost of fetching matching rows.
• Indexes are not magic: if a query returns a large share of the table, the planner may correctly choose a sequential scan because reading the table once is cheaper than thousands of random index lookups.
• In real systems, a missing index can turn a 20-50 ms query into 1-5 seconds on a table with millions of rows.
• Connection pools are often small, commonly 10-30 per app instance. If every request holds a connection while doing slow SQL, the pool itself becomes a queue.
• Stale table statistics can mislead the planner, so running ANALYZE after big data changes matters in databases that use statistics to choose plans.

When to choose one fix over another

• Use an index when the query filters on a selective column or needs rows in a specific order.
• Rewrite the SQL when a function, cast, or leading wildcard prevents index use.
• Use batching when the API makes many small queries for one request.
• Use locking or transaction review when the plan is fine but the query waits behind another transaction.

Real-World Story: In a checkout service for an online store, the endpoint GET /orders?customerId=... started timing out during a sale. The API used to return in about 80 ms, but p95 jumped to 3-4 seconds after traffic grew. Tracing showed most of the time was inside one SQL query that fetched the newest orders for a customer.

The query looked simple, but it was written with DATE(created_at) in the filter and it sorted a large set before applying LIMIT 20. That meant the database could not use the date column efficiently, so it scanned far more rows than expected. At the same time, a background job was updating the same table, which caused occasional lock waits and made the latency spikes even worse.

What went wrong: users saw spinning loaders, then retries, then duplicate support tickets because they assumed the site was down. The logs showed long SQL times, increased connection pool waits, and a sharp rise in rows scanned. The fix was to add a composite index on (customer_id, created_at DESC), rewrite the date filter as a range, and reduce the number of separate SQL calls in the request. After that, the endpoint returned to sub-100 ms behavior and the spikes disappeared.

Follow-up & Tricky Questions:

• How do you prove the DB is the bottleneck? I compare app time, DB time, and lock or pool wait time in a trace or slow query log. If the SQL span dominates p95 latency, the database is the right place to focus first.
• What does EXPLAIN ANALYZE tell you that EXPLAIN does not? EXPLAIN shows the planned strategy, but EXPLAIN ANALYZE actually runs the query and shows real execution time, row counts, and loop counts. That makes it much better for debugging slow SQL.
• Why can adding an index make writes slower? Every insert, update, or delete must also maintain the index, so more indexes mean more work on writes. The trade-off is worth it only when the read speedup matters enough.
• When would you rewrite a query instead of adding an index? If the query uses a function, casts a column, or returns too much data, rewriting is often the real fix. An index cannot fully rescue a badly shaped predicate.
• How do you handle N+1 in SQL-driven APIs? I batch the lookups, join where appropriate, or fetch related rows in one query instead of one query per item. The goal is to turn many round trips into one predictable round trip.
• Is a full table scan always bad? No. If the query needs a large portion of the table, a sequential scan can be cheaper than many random index lookups. The optimizer can be right even when the word scan sounds scary.
• Does LIMIT always make a query fast? No. If the database still has to sort or inspect many rows before it can know the top 20, LIMIT alone does not save you. A matching index is what makes the limit cheap.
• Can too many indexes hurt an API? Yes, because they slow writes and can confuse maintenance if they overlap badly. A small number of well-chosen indexes is usually better than many guessed ones.

Common Mistakes:

• Guessing instead of measuring. Correct it by using traces, logs, and EXPLAIN ANALYZE before changing anything.
• Adding an index without checking the query shape. Correct it by matching the index to the actual WHERE and ORDER BY columns.
• Fixing only one query when the request fires many SQL calls. Correct it by checking for N+1 patterns and batching work.
• Ignoring lock waits and connection waits. Correct it by checking long transactions, pool saturation, and blocked sessions, not just CPU.

Memory Hook: Think of SQL like a librarian searching a shelf: with the right index, she goes straight to the page; without it, she reads every book until she finds the sentence.

Cheat Sheet:

• Prove the slowness is in SQL, not in the app.
• Use EXPLAIN ANALYZE to see the real plan and timing.
• Look for scans, sorts, bad joins, N+1 queries, and locks.
• Add the right index only when the query shape matches it.
• Rewrite non-sargable filters into index-friendly ranges.
• Re-test p95 latency after every fix.

Practice Tasks:

• Run a simple query on a table with and without an index, then compare the plans.
• Rewrite a query that uses a function in the WHERE clause into a range-based filter.
• Take an API that makes three SQL calls and batch it into one query.