I’m trying to optimize a Spring Boot and JPA based blog application.

I’m struggling with the performance of a query that retrieves all posts from a board and its sub-boards.

At first, it took 44 seconds to fetch pagination data out of 5 millions data. After implementing DTO projection and index, it improved a little bit but it still takes 18 seconds for 10m data.

Are there any advanced optimization techniques I should consider or any things I miss?

I have hard using Slice would be faster but for this, I would like to use Page and I could use a cache mechanism but I would like to reduce the time first.

Here are the related codes

Member entity

public class Member extends BaseEntity
{
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(unique = true, nullable = false)
    private String nickname;

    @Column(unique = true, nullable = false)
    private String email;

    @Column(nullable = false)
    private String password;

    @Column(nullable = false, name = "score")
    private int score;

    private String role;

    private String url;

    private String role;

    private boolean isRemoved;
}

Board entity

public class Board extends BaseEntity
{
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String name;

    @Column(nullable = false)
    private Boolean isAdminOnly;

    @Column(nullable = false)
    private Integer orderIndex;

    @ManyToOne(fetch = FetchType.LAZY)
    private Board parent;

    @OneToMany(mappedBy = "parent")
    private List<Board> children = new ArrayList<>();
}

Post entity

@Table(name = "POST",
        indexes = {
                @Index(name = "idx_post_board_id_id", columnList = "board_id, id"),
                @Index(name = "idx_post_member_id", columnList = "member_id"),
                @Index(name = "idx_post_title", columnList = "title")
        }
)
@Setter
@Getter
@Entity
public class Post extends BaseEntity
{
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seqPost")
    @SequenceGenerator(name = "seqPost", sequenceName = "seq", initialValue = 1)
    private Long id;

    @Column(nullable = false)
    private String title;

    @Column(columnDefinition = "LONGTEXT")
    private String content;

    @Column(nullable = false)
    private int voteCount;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "member_id", nullable = false)
    private Member member;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "board_id", nullable = false)
    private Board board;

    @OneToMany(mappedBy = "post")
    List<Comment> comments = new ArrayList<>();
}

Repository

@Query("SELECT new com.payload.response.post.PostMultiResponse(" +
            "m.id, " +
            "m.nickname, " +
            "b.id, " +
            "b.name, " +
            "p.id, " +
            "p.title, " +
            "p.content, " +
            "p.voteCount, " +
            "SIZE(p.comments) , " +
            "p.createdAt) " +
            "FROM Post p " +
            "JOIN Member m ON m.id = p.member.id " +
            "JOIN Board b ON b.id = p.board.id " +
            "WHERE p.board.id IN :boardIds " +
            "AND (:keyword IS NULL OR LOWER(p.title) LIKE CONCAT('%', :keyword, '%')) " +
            "ORDER BY p.id DESC")
    Page<PostMultiResponse> findAllPostByBoardId(@Param("boardIds") List<Long> boardIds, 
                                                 @Param("keyword") String keyword, 
                                                 Pageable pageable);