Part 2: Optimizing Search Performance"

#backend #database #java #performance

Intro

As data grows, search speed becomes a major bottleneck. In this post, I will share how I optimized product search from 1.8s to 0.5s using QueryDSL and Database Indexing.

1. The Challenge: Dynamic Queries

Users want to filter products by category, price, and rating, while sorting by sales or newest arrival.

The Problem: Writing raw SQL for every combination is impossible to maintain.
The Goal: Build a type-safe, dynamic search engine that stays fast even with 1M+ records.

2. Implementation: QueryDSL

I used QueryDSL to handle complex filtering logic without losing type safety.

public Page<Item> searchItems(ItemSearchCondition condition, Pageable pageable) {
    return queryFactory
        .selectFrom(item)
        .where(
            nameContains(condition.getName()),
            categoryEq(condition.getCategoryName())
        )
        .orderBy(getSortOrder(condition.getSortType()))
        .offset(pageable.getOffset())
        .limit(pageable.getPageSize())
        .fetch();
}

3. Performance Tuning: Indexing

Even with good code, a query is slow if it scans the entire table.

The Problem: Sorting 1 million rows took over 1.8 seconds.
The Solution: I analyzed the execution plan and applied Composite Indexes on frequently used columns (e.g., Category + SalesVolume).
The Result: Latency dropped to under 0.5s (72% improvement).

4. Advanced: N+1 Problem

When fetching items with their categories, Hibernate often executes too many queries. I solved this by using fetchJoin(), reducing the database load significantly.

Conclusion

Optimizing search is not just about writing queries; it's about understanding how the database reads data. In the next part, we will dive into high-concurrency coupon systems.

DEV Community