Diagnosing and Fixing Slow Database Queries in Spring Boot
Fast recall
Quick revision
Core answer
Your ability to systematically diagnose slow queries end-to-end — from detection in logs through query analysis to code-level and schema-level fixes.
Going straight to adding an index without checking the execution plan, or fixing the symptom without understanding the cause.
Mention enabling slow query logs, using EXPLAIN ANALYZE, checking for N+1 with Hibernate statistics, and adding covering indexes vs. partial indexes based on the query pattern.
Interview-ready guide
Interview answer
When a Spring Boot endpoint is slow and the database is involved, the first suspects are always N+1 queries and missing indexes. These two problems cause the majority of database-related latency in Spring Boot applications.
Step 1 — Enable SQL logging
spring.jpa.show-sql=trueandlogging.level.org.hibernate.SQL=DEBUGlog every SQL statement;logging.level.org.hibernate.orm.jdbc.bind=TRACElogs parameter values- Count the number of SQL statements for a single HTTP request — more than 5-10 for a simple read suggests N+1; one query per entity in a collection fetch is the giveaway pattern
Detecting N+1
- The symptom: fetching 50 orders generates 51 SQL statements — 1 for the orders list, 50 for their items; Hibernate's lazy loading triggers a SELECT per entity
- Fix:
JOIN FETCHin JPQL:SELECT o FROM Order o JOIN FETCH o.items WHERE o.status = 'ACTIVE'; or@EntityGraph(attributePaths = {"items"})on the repository method; or@BatchSize(size = 25)for collections that can't be JOIN FETCHed without a Cartesian product
Detecting missing indexes
EXPLAIN ANALYZE(PostgreSQL) orEXPLAIN(MySQL) on the slow query; look forSeq Scan(PostgreSQL) ortype: ALL(MySQL) on large tables — these mean no index was used- Add an index on the WHERE clause column:
CREATE INDEX CONCURRENTLY ON orders(customer_id)in PostgreSQL; verify with EXPLAIN ANALYZE that the plan changes toIndex ScanorIndex Only Scan
Slow query log
spring.jpa.properties.hibernate.generate_statistics=truelogs query count and time per session; configure the database's slow query log to capture queries taking over 1 second- Capture the slow query and run EXPLAIN ANALYZE in a database client with realistic data; the execution plan on a test database with 100 rows doesn't reveal the plan the production database with 50M rows uses
Diagnosing and Fixing Slow Database Queries in Spring Boot
Direct Answer
Enable slow query logging, capture the query, run EXPLAIN ANALYZE, identify missing indexes or N+1 patterns, fix at the query or schema level, and verify with re-execution.
Common Mistakes & Pitfalls
Going straight to adding an index without checking the execution plan, or fixing the symptom without understanding the cause.
Check your understanding
Answer these without looking back at the article.
- How do you detect N+1 queries in a Spring Boot application automatically?
- When would you choose a covering index over a regular index?
- How does Hibernate second-level cache help with slow queries?
Related interview prep categories
1Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.