Question 20832 in Mysql Question posted in
The official MySQL documentation can be found here.

Spring Boot JPA Native Query Object Mapping – Mysql

I have a Model called CommunityProfile. This model contains two child relationships; player (type User), and rank (type Rank).

The default spring boot JPA-generated query is taking approximately 9s to fetch 200 records, which is rather slow. By using the following MySQL query, I can return the data I need rather quickly:

SELECT cp.*, r.*, u.* FROM community_profiles cp
LEFT JOIN users u ON cp.player_id = u.id
LEFT JOIN ranks r ON cp.rank_id = r.id 
WHERE cp.community_id = 1

How can I make my repository map the results to their correct Objects/Models?

I have tried using a non-native query, like this:

    @Query("SELECT cp FROM CommunityProfile cp " +
            "LEFT JOIN FETCH cp.player u " +
            "LEFT JOIN FETCH cp.rank r " +
            "WHERE cp.communityId = :communityId")
    List<CommunityProfile> findByCommunityIdWithJoin(@Param("communityId") Integer communityId);

However, this is still quite slow in comparison, resulting in an 800-900ms response. For comparison, my current Laravel application can return the same data in a 400-ms cold start.

Any tips are appreciated, thank you

==UPDATE==

After trying the suggested @Index annotation, I still don’t really see any performance gains. Did I implement correctly?

@Entity
@Table(name = "community_profiles", indexes = @Index(name = "cp_ci_idx", columnList = "community_id"))
public class CommunityProfile {
Tags:

2

Answers


  1. 0

    If your JPA query is working, and you are just asking about performance, you may add the following index:

    CREATE INDEX idx ON community_profiles(community_id);

    This index should allow MySQL to filter off records which are not part of the result set.

    From JPA itself you may use:

    @Table(indexes = @Index(name = "idx", columnList = "community_id"))
public class CommunityProfile {
    // ...
}
    Login or Signup to reply.
  2. 0

    Have you tried EntityManager

           @PersistenceContext(type = PersistenceContextType.TRANSACTION)
             private EntityManager entityManager;

            List<CommunityProfile> findByCommunityIdWithJoin(Integer communityId){
              String query = ""SELECT cp FROM CommunityProfile cp " +
                    "LEFT JOIN FETCH cp.player u " +
                    "LEFT JOIN FETCH cp.rank r " +
                    "WHERE cp.communityId = :communityId"
            List<CommunityProfile> list = entityManager.createNativeQuery(query, CommunityProfile.class)
                            .setParameter("communityId",communityId)
                            .getResultList();
           entityManager.clear();
           return list
        }

    Once I used this kind of native query inside loop and it constantly returned cash values bu entityManagaer.clear() clears cash. This is for info only)

    Or create an Index on specific columns when you are defining entity classes like:

      @Table(indexes = {
  @Index(columnList = "firstName"),
  @Index(name = "fn_index", columnList = "firstName"),
  @Index(name = "mulitIndex1", columnList = "firstName, lastName")
  ...
}

    For Non-entity @Index you can check documentation

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search