JPA @ManyToMany join table indexing

Question

Hibernate allows adding indexes on @ManyToOne mappings by the use of @org.hibernate.annotations.Index.

Is there a way to specify index for the join table in a @ManyToMany relation?

If Entity A and Entity B have a @ManyToMany with A being the owning side, the join table will have a composite index (a1, b1).

I wonder if this is enough, or do I need to create another index (b1, a1)?

Answer 1

Answering a 6 year old question here, still relevant though. I came across this question while facing the same issue. Searching the web makes it look like JPA does not support indexes on join tables but it does, this is documented here.

When using @JoinTable you can specify the indexes on the annotation, for example

@ManyToMany()
@JoinTable(name = "car_driver",
    joinColumns = @JoinColumn(name = "car_id"),
    inverseJoinColumns = @JoinColumn(name = "driver_id"),
    indexes = {
        @Index(name = "idx_car_driver_car_id", columnList = "car_id"),
        @Index(name = "idx_car_driver_driver_id", columnList = "driver_id")
    }
)
private Set<Driver> drivers;

Answer 2

I thought about it and I find solution. In case of many to many relation I used

@ManyToMany
@JoinTable(name = "core_auth_role",
    joinColumns = { @JoinColumn(name = "auth_id") },
    inverseJoinColumns = { @JoinColumn(name = "role_id") },
    uniqueConstraints = { @UniqueConstraint(columnNames = {"role_id", "auth_id" }) })
@ForeignKey(name = "fk_testkey", inverseName = "fk_testkey_inverse")
private Set<Role> roles;

In result I have two idexes (auth_id, role_id) and (role_id, auth_id). Schema created by Hibernate:

create table SOME_TABLE ( 
    ...
    primary key (auth_id, role_id),
    unique (role_id, auth_id)
);

primary key and unique cosntrainst are automatically indexed in database.

Answer 3

I too am searching for this - the holy grail, it seems.

In this post: How can I ask Hibernate to create an index on a foreign key (JoinColumn)? an answerer seems to believe that adding @Index to the collection creates an index on the join table. It doesn't.

This post: How do I create an index on join tables using Hibernate annotations? reveals what I am slowly coming to believe is the horrible truth. It's not possible, unless you revert to using an hbm.xml file to define your entities (ie: doesn't appear to be possible using annotations).

So whilst it does speed up queries tremendously to have a composite index on join tables, encompassing both FK columns, but there is currently no way to create said index automatically using annotations.

I am now looking into creating a task to be used after Hibernate's ExportSchema execution to identify join tables and create indexes. It's not going very well! Please do share any viable solutions you happen to come across. I'm hoping someone will share their approach for comparison.

Answer 4

I came across this https://www.baeldung.com/jpa-many-to-many while looking for a way how to have control over the join table, so that I could have at least one of its keys indexed.

It breaks down the many-to-many relation into a many-to-one relation on the join table side and a typical OneToMany on each of the two associated entities, associating them to the join table, like so:

@Entity
class Student {

    @OneToMany(mappedBy = "student")
    Set<CourseRating> ratings;
}

@Entity
class Course {

    @OneToMany(mappedBy = "course")
    Set<CourseRating> ratings;
}

@Entity
class CourseRating {

    @ManyToOne
    @MapsId("student_id")
    @JoinColumn(name = "student_id")
    Student student;

    @ManyToOne
    @MapsId("course_id")
    @JoinColumn(name = "course_id")
    Course course;       
}

The @Index annotation can be used on any of the fields in the CourseRating (join table) entity. In my case I used a composite key on the join entity because of reads on its table, so I actually needed to split the relation.

I hope this can be of help.