Placing indexes on a MySQL table using JPA/Hibernate

July 3, 2022

I would like to set indexes on multiple columns within a single table in MySQL database. After reading this article, I’m not 100% sure which approach to use.

So my (simplified) table looks like this:

@Data
@Entity
@SuperBuilder
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "loan")
public class Loan {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "loan_id", unique = true, nullable = false)
    private long id;

    @Column(name = "amount", unique = false, nullable = false)
    private double amount;

    @Column(name = "rate", unique = false, nullable = false)
    private double rate;

    @Column(name = "payments", unique = false, nullable = false)
    private int payments;

    @Column(name = "pmt", unique = false, nullable = false)
    private double pmt;
}

I will have a lot of search queries, for instance:

SELECT * FROM Loan loan 
WHERE loan.amount =: amount AND loan.rate =: rate AND loan.payments =: payments AND loan.pmt =: pmt
LIMIT 1;

Now, I would like to index fields in WHERE clause. Essentially, I would like to achieve effect of a "composite key" where in table loan there are only unique combinations of mentioned fields. So I cannot have two rows all with some values.

Is there such a configuration?

>Solution :

ou can add a UNIQUE constraint, which would be indexed automatocally

@Table(uniqueConstraints = 
  { 
  @UniqueConstraint(name = "UniqueWhereclause", columnNames = { "amount", "rate","payments","pmt" })})

or you can create an index alone

@Entity
@SuperBuilder
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "loan", indexes = {
    @Index(columnList = "amount, rate,payments,pmt", name = "name_idx") })