Need to put @Index even when I marked with @Column(unique=true) ?

Question

Hello,

Need to put @Index even when I marked with @Column(unique=true) ?

I have a property that will be used frequently to retrieve the entity and wanted to make it an index column on the database. So this property is already marked with @Column(unique=true), do I need to put @Index?

thanks

Answer 1

Most databases do implement UNIQUE constraints using a UNIQUE INDEX, but they aren't required to and a UNIQUE constraint does not necessarily give you the benefits of an index. In theory, a constraint would not be considered by the query planner whereas an index would be.

That said, in the particular case of MySQL, it seems that a UNIQUE constraint and a UNIQUE INDEX are "synonymous".

But you should confirm that by checking the query plan.

Answer 2

I presume you are using mysql as the question is tagged with mysql

If you are using annotations and something like this @Column(unique = true)

Then this gets converted to the following DDL by hibernate unique (user_id)

When you query the mysql db and do show index from It would show user_id as an indexed field, so the answer is @unique is enough for the field to be indexed

HTH