Hibernate: When to use @Index annotation

Question

In my scenario, I have a schema generation script to create tables and required indexes. I am wondering is there any need to define @Index annotation in hibernate entities as well, if so why?

Script:

create table issues (id, project_id, .., status_id)

create index idx_issues_projid on issues (project_id)

Entity:

@Table(name="issues")
public class DBIssue {

..
  @ManyToOne(fetch = FetchType.EAGER)
  @JoinColumn(name = "PROJECT_ID")
  @Index(name="INDEX_TFW_ISSUE_PROJECT_ID")
  private DBProject project;
}

Hibernate configuration:

<property name="hibernate.hbm2ddl.auto">off</property>

Answer 1

I presume you're asking about the Hibernate @Index annotation, which has essentially been imported into JPA 2.1. You would use @Index anywhere you would otherwise proactively tell a relational database to index a column, primarily on a field where you know you'll be doing lots of lookups. In this case, for example, you are probably going to want to "select the DBIssues belonging to a particular DBProject frequently, so it would make sense to index that column in the table holding DBIssue.