@Index in JPA: Difference between having indexes on each column or having index on all columns toget-CodePudding

I have an entity to which I want to add indexes, using @Index annotation within @Table in JPA. I would appreciate some clarification on what the difference is between these two approaches:

@Table(name="people", indexes = {
        @Index(columnList = "name"),
        @Index(columnList = "age")
})

@Table(name="people", indexes = {
        @Index(columnList = "name, age")
})

Or both together:

@Table(name="people", indexes = {
        @Index(columnList = "name"),
        @Index(columnList = "age"),
        @Index(columnList = "name, age")
})

My intention is to speed up the database access when using a JPA method like

peopleRepository.findByNameAndAge(name, age)

and possibly also one field only like

peopleRepository.findByName(name)

I would very much appreciate an explanation of each and whether certain approaches are better than others if I want to use the above JPA methods.

CodePudding user response：

Creating index on list of columns instead of on each column separately is helpful when you wish for it to be unique. So in your example if you allow only one person with given name be X old using unique index on column list is the way to go. If not it's better to create them separate on each column cause that's sufficient to make selects quicker.

Be aware that indexes slow down inserts and updates and they take away storage so it's better to use them only where it's actually helpful. Another thing is you might rethink age as database column, it's better to keep birth date so you don't have to recalculate it.

CodePudding user response：

Not all RDBMS are handling multi-columns indexes with the same efficiency: if SQLServer, ORACLE and DB2 are quite smart, it's not always the case with others like MySQL and PostgreSQL. Anyway the decision of what to index and how should be done in regard of the use cases: what are the search criteria required by the application? to support UI or reporting? How often the related queries will be run? etc. (And seriously indexing "age"? a column - if any - that should be "virtual" and not stored...)