How to create indexes on multiple columns

Question

We have the following entity relationships where a User belongs to a particular Organization. My queries either look like "select * from User where org=:org" or "select * from User where org=:org and type=:type"

I have separate indexes on the User class. The first query will be fine, because of the Index on the foreign key element. Does the second query mandate a multi columnindex on org and type columns. If so how should I annotate to create one such index.

@Entity 
class User {
...

@ManyToOne 
@ForeignKey
@Index
Organization org;

@Index
Type type;    
...
}

Answer 1

This is doable using the Hibernate specific @Table annotation. From the documentation:

2.4.1 Entity

...

@Table(appliesTo="tableName", indexes = { @Index( name="index1", columnNames={"column1", "column2"} ) } ) creates the defined indexes on the columns of table tableName. This can be applied on the primary table or any secondary table. The @Tables annotation allows your to apply indexes on different tables. This annotation is expected where @javax.persistence.Table or @javax.persistence.SecondaryTable(s) occurs.

Reference

• Hibernate Annotations Reference Guide
  • 2.4. Hibernate Annotation Extensions