I need to index a column in my SQL DB. There is one way to use a SQL query to create the index on a particular column

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Another way I found on the internet is the use of something like this in JPA

@Table(indexes = @Index(columnList = "firstName"))

Is there any difference between the two? If JPA approach is being followed in real world won't it try to create index each time the project is deployed?

CodePudding user response：

Short answer

Use SQL to create/update the schema because it gives more flexibility and maintainability in future migrations, and it is less error-prone.

Long answer:

The @Index annotation is metadata used by the schema generation process to automatically create the database schema.

"If JPA approach is being followed in real world won't it try to create index each time the project is deployed?"

Yes, it will try to create the index and update the schema each time. That is convenient in non-production, but it is bad practice in production.

CodePudding user response：

For different database indexes creation, SQL queries may vary therefore those SQL queries will be database dependent. For different databases, you may need to write different SQL queries. Whereas @Index is specific to JPA and independent from the database. The same @Index can be used to create an index in a different database.

The @Index will perform its work only while schema generation. In project, we can specify schema generating properties spring.jpa.hibernate.ddl-auto and set to update which is used to update the schema or none in the production.

spring.jpa.hibernate.ddl-auto = update