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)?

score 13 · Answer 1 · answered Dec 19 '17 at 19:20

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;

This works on '@ManyToMany but not on '@ManyToOne (as required in the original question). At least not in Hibernate 5.4.10.Final. — dpelisek, Jun 02 '20 at 06:57
@dpelisek It's the other way around. OP knows it works on '@ManyToOne and is looking for a solution working on 'ManyToMany. — Echox, Feb 26 '21 at 10:39

score 7 · Accepted Answer · edited May 23 '17 at 11:59

7

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.

edited May 23 '17 at 11:59

Community

1
1

answered Jan 04 '12 at 10:26

ndtreviv

3,473
1
31
45

No updates, I assume? :( Hibernate 4.3+ is not an option for me, otherwise _BTW in JPA 2.1 / upcoming Hibernate 4.3 @javax.persistence.JoinTable can contain indexes, this may do what you want._ [link](https://hibernate.atlassian.net/browse/HHH-4263) – RobertG Oct 28 '15 at 14:06
1

Wow. This was so long ago I can't actually remember. In the end we started using liquibase to generate our database, so I believe we just put the add index statements in there instead. Sorry I can't help anymore! – ndtreviv Oct 30 '15 at 10:07
:D We also agreed on using liquibase for "manually" adding the indexes, in the meantime. Thx for the input! – RobertG Nov 03 '15 at 14:32
Totally possible, look at the Jopr's answer! – Filipe Mendes Oct 27 '20 at 19:02

score 1 · Answer 3 · edited Aug 05 '19 at 16:13

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.

felipeheld · Answer 4 · 2020-02-12T00:02:13.600

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.

JPA @ManyToMany join table indexing

4 Answers4