use index for crudRepository method

Question

Currently, my project is working well, but I need to use index for my query to speed up the data retrieval from database.

Given that:

my table script:

create table tbl_my_table (
  cycle_date date not null,
  seq_no int not null,
  username varchar(50) not null,
  last_update_time datetime not null
)
go

alter table tbl_my_table add constraint tbl_my_tablePK primary key (cycle_date, seq_no )
go

create nonclustered index tbl_my_table_idx on tbl_my_table (cycle_date,username)
go

my Entity class:

@Entity
@Table(name = "tbl_my_table")
@IdClass(MyTablePK.class)
public class MyTable implements Serializable{

  private static final long serialVersionUID = 3419263311932213654L;

  @Id
  @Column(name = "cycle_date", nullable = false, columnDefinition="date")
  private Date cycleDate;

  @Id
  @Column(name = "seq_no", nullable = false, columnDefinition="smallint")
  private Integer seq_no ;

  @Column(name = "username", nullable = false, columnDefinition="varchar(50)")
  private String username;

  @Column(name="last_update_time", nullable = false, columnDefinition="datetime")
  private DateTime last_update_time;

   (with getters & setters, equals and hashcode, and toString methods)
}

My repository or DAO:

public interface MyTableDAO extends CrudRepository<MyTable, MyTablePK> {
    List<MyTable> findByCycleDateAndUsername(
            Date cycleDate, String username);
}

With CrudRepository, I understand that I do not need to write a query to retrieve some records. The above method findByCycleDateAndUsername is "magically" translated to query: "select a from MyTable a where a.cycleDate = :cycleDate and a.username = :username"

Question: How do I apply the index tbl_my_table_idx to this method findByCycleDateAndUsername() so that it can return result in a faster manner?

condition and restriction: I am working on a java project, run on java6, spring-boot 1.3.5, using hibernate, sybase database, maven web application.

p/s: please no chit-chat, just written solution. p/s/s: please don't advise me to change my programming language, framework or upgrade my java version. It is a restriction for my work.

Zeromus · Accepted Answer · 2017-04-21 09:53:04Z

2

you mean you want to force the index? like SELECT * FROM Table WITH(INDEX(Index_Name)) ?

if not then don't worry: the magic is just in generating the query that will be then executed like any other query on db.

It's up to the db query plan to choose whether or not to use the index

answered Apr 21, 2017 at 9:53

Zeromus

4,5678 gold badges34 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

terencefung Over a year ago

hm... so you are saying that I will need to write the query in native SQL if I want to force the index, correct? Then is it possible to force the index using HQL? if yes, please write a simple hql example with forcing the index. Thanks!

terencefung Over a year ago

if no, then I guess, there is no support for specifying index hints in HQL yet.

Zeromus Over a year ago

to my understanding there is no support for index hint in hql... you have to use native query

Collectives™ on Stack Overflow

use index for crudRepository method

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related