We run into a problem regarding the performance of replaying events. Currently we have an event store with > 1.000.000 events. Our db is Mysql. We have created a simple replaying service which replays the events and uses the visitEvents method from the JpaEventStore to select, deserialize and upcast the events.
The problem lies in the fact that the query to select events in batches is very slow. The query probably uses limit and offset (which is known to be slow with a large number of records.)
I’m wondering whether there is a way we can influence the actual query that is performed on the database.
The query below (using late row lookups) is a lot quicker (~4 seconds compared to 80+ seconds:
select a.* from DomainEventEntry a
inner join (select eventIdentifier from DomainEventEntry limit 100 offset 100000) b
on a.eventIdentifier = b.eventIdentifier where payloadType = ‘x’