We recently encountered some slow queries on our audit tables that is caused by statements similar to

select e

from package.Child_AUD e

where e.parent_id = :parent_id and e.originalId.REV.id =

(select max(e2.originalId.REV.id)

from package.Child_AUD e2

where e2.originalId.REV.id <= :revision and e.originalId.id = e2.originalId.id) and REVTYPE != :delrevisiontype

We traced this query to the loading of OneToMany relationship between Parent and child.

I'm aware of the ValidityAuditStrategy but decided against using it initially since we'd rather keep our saves faster.

Im exploring the possibility of indexing the envers columns which corresponds to the FKs of the real table as an alternatice.

In this example adding an index on Child_AUD.parent_id. This does improve the query plan on mysql from doing a full table scan to using the index.

For now, we have some custom code that will generate these indices as part of the hbm2ddl process.

What are your thoughts on this as an alternative to ValidityAuditStrategy?

Are there scenarios where these indices will not produce an improvement? One scenario I can think of is if an entity changes a lot such that an index on e.parent_id will match a lot of records. Although on our use case, we dont have such an entity yet.

If this is a viable solution, is there some plan on including it in a future release?