Query Performance Improvement Template Search

Step-by-Step Improvement of Query Average Speed

Final Improvement: 26s -> 16s

Queries Executed in the Search API

When a search API request is made, the following four query methods are executed:

• TemplateJpaRepository.findAll()

  • Fetches "templates" along with the "categories," resulting in an N + 1 problem.

• TemplateTagJpaRepository.findAllByTemplate()

  • Fetches tags for each template individually.
  • Fetches "template tags" while querying "tags," resulting in an N + 1 problem.

• ThumbnailJpaRepository.fetchByTemplate()

  • Fetches thumbnails for each template individually.
  • Fetches "thumbnails" while querying "source codes," resulting in an N + 1 problem.

• MemberJpaRepository.fetchByTemplateId()

  • Fetches the author member of the template.
  • Contains unnecessary logic.

The total number of templates is N, the total number of categories is C, the average number of tags per template is T, and the average number of source codes per template is SC. The following number of queries will be executed:

-> O(N * (C + T + SC))

1. Improving the N + 1 Problem

Improving Category Retrieval for N + 1 Problem

Before Improvement

• Each of the M "templates" fetches the "category" once.

Improving Tag Retrieval for N + 1 Problem

TemplateTagJpaRepository.findAllByTemplate() -> TemplateTag(1) + Tag(N)

Before Improvement

• Each of the M "templates" fetches "template tags" once.
• For the N "template tags" fetched, queries the "tags."

After Applying Fetch Join

@Query("""
        SELECT tt, t
        FROM TemplateTag tt
        JOIN FETCH tt.tag t
        WHERE tt.id.templateId = :templateId
        """)
List<TemplateTag> findAllByTemplate(Template template);

---

Improving Thumbnail Retrieval

Before Improvement

After Applying Fetch Join

@Query("""
        SELECT t, sc
        FROM Thumbnail t
        JOIN FETCH t.sourceCode sc
        WHERE t.template = :template
        """)

2. Improving Logic

• Fetch the Template's TemplateTags into a List.
• Fetch the Template's Thumbnails into a List.

Before Improvement

After Improvement

3. Creating Full-Text Search Index

ALTER TABLE template ADD FULLTEXT INDEX idx_template_fulltext (title, description);
ALTER TABLE source_code ADD FULLTEXT INDEX idx_source_code_fulltext (content, filename);