I know that I can create an index for both a single column and a composite one. A also I can make an index on some expression:

• create an index on an expression that searches for data by some condition, while first I specify that the fields first need to be sorted and then output the result by condition, and based on this expression, an index is built.

(like an index when working with Couchbase)

CREATE INDEX `us_users_sorted` ON
`user_profile`(
    `_class`,
    `enabled`,
    `countryCode`,
    `firstName` DESC,
    `lastName` DESC)
WHERE (((`_class` = "com.cb.demo.userProfile.model.UserEntity")
    and (`enabled` = true))
    and (`countryCode` = "US"))

But I can't find information about this :

1. If I create a record, will I have to call again in the same transaction to create an index for this data (the index is described above) ?
2. When deleting a record, will I also have to trigger the creation of this index again ?
3. When updating , will I have to do this too ?
4. Should I do this via nativeQuery() if I use Spring Data Jpa ?

I don't see the point in separating this into several questions, in fact this is a question about working with indexes using Spring and a relational database.

Maybe someone on the web has seen a detailed example, with all the nuances, when using complex relational database indexes (I don't mean Couchbase) and Spring data jpa (spring boot)?

If possible, give an answer with an example, please

CodePudding user response：

The index is only need to be created for all the existing records for one time. After that , if an DB record is added/updated/deleted , it just needs to add/update/removed such record from the index rather than creating the whole index again for all records.

You can think that an index just like there exist another HashMap which allow you to quickly retrieve a record by some keys . But the DB will help to maintain the content of this HashMap automatically whenever a record is added/updated/deleted in the table that is related to this index. Creating an index just like you initialise the content of this HashMap which only done for one time.

So you should not create an index through native query .You should create it separately upfront before you start the application. Just like you create an DB table.