JpaEventStore and indexes for optimal performance

I’m using JpaEventStore and I noted that DomainEventEntry and SnapShotEventEntry both have a composite primary key (“AggregateIdentifier”, "SequenceNumber ", “Type”).
I wonder if this is the only index needed for optimal performance in Axon standard operations.

I imagine if I replay events using visitEvents and add some criteria, an index based on the criteria could be useful, but there are other operations requiring indexes?
Replaying without criteria is already optimized? In Axon source code I found the select used for replaying:

“SELECT new org.axonframework.eventstore.jpa.SimpleSerializedDomainEventData(”

  • "e.eventIdentifier, e.aggregateIdentifier, e.sequenceNumber, "
  • "e.timeStamp, e.payloadType, e.payloadRevision, e.payload, e.metaData) "
  • "FROM DomainEventEntry e %s ORDER BY e.timeStamp ASC, "
  • “e.sequenceNumber ASC, e.aggregateIdentifier ASC”

There are even conditions on timeStamp, sequenceNumber and aggregateIdentifier:

sb.append("((")
.append(“e.timeStamp > :timestamp”)
.append(") OR (")
.append(“e.timeStamp = :timestamp AND e.sequenceNumber > :sequenceNumber”)
.append(") OR (")
.append("e.timeStamp = :timestamp AND e.sequenceNumber = :sequenceNumber AND ")
.append(“e.aggregateIdentifier > :aggregateIdentifier))”);

Is a composite index on (“timeStamp”,“sequenceNumber”,“aggregateIdentifier”) the best solution?
Or better 3 indexes, one for each field?
Or even better all 4?

(My DB is Oracle)

Andrea

Hi Andrea,

the best you can do is to check the queries (explain) and see which indexes a database will use. I have done that on MySQL, and have put the results in the last chapter of the reference guide (www.axonframework.org/docs).

Hope this helps.
Cheers,

Allard