Creating an Index with Hibernate

The job of creating a SQL Index is rightly the job of a DBA. However, sometimes the need for an index is blindingly obvious and only a mad DBA would miss the opportunity to create a lookup.

Unfortunately, annotations for index hinting did not make it into the JPA 2 specification.

In this post we’re highlighting a Hibernate annotation - @org.hibernate.annotations.Index - that hints for the creation of an index on various columns.

Consider a TABLE that has been constructed for doing lookups between two COLUMNs - and the search can happen on either COLUMN. If one is already assigned to be the @Id, lookups in the opposite direction will be horrendously slow, especially as the database grows.

The following Hibernate annotation will ensure that the myName column is given an index

@org.hibernate.annotations.Index(name = "myNameIndex")
private String myName;

which will result in the equivalent of typing the following native SQL command

CREATE INDEX myNameIndex ON MyEntity(myName);

optimising queries such as

em.createQuery(
    "SELECT e FROM MyEntity e WHERE e.myName = :myName"
    ).setParameter("myName", myName).getResultList();

If you are using a @OneToMany Collection with a mappedBy entry, then you should probably consider indexing the mappedBy property - you’ll notice an instant speedup in the retrieval of your entities.

An alternative way to define the index is to include everything in the supplementary @Table annotation. This approach also allows for a multi-column index

@Entity
@org.hibernate.annotations.Table(
    name="Forest",
    indexes = { @Index(name="idx", columnNames = { "name", "length" } ) }
    )
public class Forest { ... }

Note that in some cases, developers should consider using @UniqueConstraint instead of building an index and allowing multiple results.