0

I am using envers at Spring boot project.

@Repository
public interface DataAudRepository extends RevisionRepository<Data, String, Integer>, JpaRepository<Data, String> {

}

If there is numerous data on the data_aud table (data number : 3500), request of findRevisions(key, pageable) is very slow.

Sure if there is not numerous data, request is argent.

When Spring call this function, some of hiberate sql occur.

select *
from data_aud publish_au0_ 
where 
    data_au0_.rev=(
        select max(data_au1_.rev) from data_aud data_au1_ 
        where 
            data_au1_.rev<=999999 and 
            data_au0_.data_id=data_au1_.data_id) AND 
    data_au0_.data_id='my-data-01'

Perhaps i think select max sequence is reason for slow request.

How to avoid this slow condition?

+)

Primary key of data_aud table is (data_id, varchar(32), rev, int(11))

and PK of data table is data_id.

Rick James
  • 135,179
  • 13
  • 127
  • 222
user7393582
  • 23
  • 3
  • My advice is not to make assumptions about performance issues but instead profile. It could anything from adding an index based on the query needs to help MySQL find records easier to finding you have a disk which has poor i/o and needs to be addressed. If you profile, please update your question with your findings. – Naros Mar 21 '18 at 13:44
  • Yes, i add P.K information. Thank you :) – user7393582 Mar 23 '18 at 01:37
  • This seems to be a "groupwise max" problem; see the tag I added. I assume you want the "latest rev" for each of several thingies, correct? – Rick James Mar 29 '18 at 00:49

0 Answers0