how to use the index with springboot or JPA

Question

Table A

ID	shortdescription	thumbnailimage	linkactions	Country_id
165	shortdescription	thumbnailimage	linkactions	E1796BA

Table B

ID	name	Country_id
1	India	E1796BA

Controller

public ResponseEntity<ContentManagePageVO> queryAllActionsWithPagination(
@RequestParam(defaultValue = "0") Integer pageNo,
@RequestParam(defaultValue = "10") Integer pageSize,
@RequestParam(required = false, defaultValue = "") String filter) {
return ResponseEntity.ok(contentManageService.queryAllActionsWithPagination(filter, pageNo, pageSize));
    }

Service

   public ContentManagePageVO queryAllActionsWithPagination(String filter, Integer pageNo, Integer pageSize) {
        Pageable paging = PageRequest.of(pageNo, pageSize,Sort.by("id"));
        Page<ContentCountryDto> contentManagePage;
       if(StringUtils.isBlank(filter)) {
           contentManagePage = contentManageRepository.findAllPaging(paging);
       }else {
           contentManagePage = contentManageRepository.findByTitlePaging(filter, paging);
       }
        if (contentManagePage.isEmpty()) {
            log.error("No record found!!! ", filter);
            throw new GenericSuccessException(StaffNotificationExceptionEnum.CONTENT_MANAGE_PAGE_SEARCH_NOT_FOUND_EXCEPTION);
        }
        ArrayList<ContentManageVO> ContentManageVOList = new ArrayList<>();
        for (ContentCountryDto contentManage : contentManagePage) {
            ContentManageVO contentManageVO = new ContentManageVO();
            BeanUtils.copyProperties(contentManage, contentManageVO);
            ContentManageVOList.add(contentManageVO);
        }
        return ContentManagePageVO.builder()
                .page(contentManagePage.getNumber())
                .pageSize(contentManagePage.getSize())
                .result(ContentManageVOList)
                .total(contentManagePage.getTotalElements())
                .build();
    }

Repository

    @Transactional(rollbackFor = Exception.class)
    @Query("select new com.model.entity.ContentCountryDto (A.id, A.position ,A.title,A.shortdescription,A.thumbnailimage, A.linkactions, A.last_updated_date, A.last_updated_by, A.country.countryId, A.country.countryName, A.date_published) " +
            "from ContentManage as A inner join A.country as B where lower(A.title) like lower(concat('%', :filter,'%') ) or lower(A.shortdescription) like lower(concat('%', :filter,'%') ) or lower(B.countryName) like lower(concat('%', :filter,'%')) or lower(A.linkactions) like lower(concat('%', :filter,'%'))")
    Page<ContentCountryDto> findByTitlePaging(String filter, Pageable pageable);

I would like to add index to retrieve data from the database more quickly. I have no idea on index, please suggest me how to achieve index in spring boot or JPA

Does this answer your question? Hibernate: Create Index

Simon Martinelli
– Simon Martinelli

2021-03-22 10:39:23 +00:00
Commented Mar 22, 2021 at 10:39 — Simon Martinelli
– Simon Martinelli, Commented Mar 22, 2021 at 10:39

Jens Schauder · Accepted Answer · 2021-03-23 08:38:50Z

The use of an index by a database engine is almost 100% independent of JPA and Spring Data JPA.

It works simply like this:

You add an index to the table as desired. You do that using SQL for your database and execute it with whatever tool you use to provision your database. Hopefully something like Liquibase or Flyway, but maybe the command line SQL tool of your database.

When a SQL statement gets executed the database will determine (i.e. guess) if the index is helpful for that statement and use it if it deemed helpful. No change of the SQL is necessary.

The basic idea of that estimate is:

the columns of the index need to be part of the filter criteria used in the table. For an exception for this rule read up on "index skip scan".
the query needs to return only a small part of the table. Because a full table scan is in most cases faster than accessing 90% of all rows via index access.

Things get more complicated once you get into joins with the many different ways a join can get executed by the database and how these get affected by the presence of an index.

qarabala · Accepted Answer · 2024-04-21 13:21:26Z

0

You can do it like this:

@Entity
@Table(indexes = {
  @Index(columnList = "firstName"),
  @Index(name = "fn_index", columnList = "firstName"),
  @Index(name = "mulitIndex1", columnList = "firstName, lastName"),
  @Index(name = "mulitIndex2", columnList = "lastName, firstName"),
  @Index(name = "mulitSortIndex", columnList = "firstName, lastName DESC"),
  @Index(name = "uniqueIndex", columnList = "firstName", unique = true),
  @Index(name = "uniqueMulitIndex", columnList = "firstName, lastName", unique = true)
})
public class Student implements Serializable

Source: baeldung.

answered Apr 21, 2024 at 13:21

qarabala

1781 silver badge12 bronze badges

Collectives™ on Stack Overflow

how to use the index with springboot or JPA

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related