pdo-event-store: Running projections causes constant full table scans due incorrect forced index

We’re developing a pretty new app using prooph, with only ~20k events in a single stream. We have ~30 projections, and when running these all at once the database gets completely destroyed.

I dived in with a debugger and figured out this is because we are doing 30x full table scans per 100ms, as every loop of each projection is doing a full table scan.

Here’s an example query:

SELECT * FROM `_4228e4a00331b5d5e751db0481828e22a2c3c8ef` USE INDEX(ix_query_aggregate)
WHERE `no` >= 19067
ORDER BY `no` ASC
LIMIT 1000;

Note the USE INDEX(ix_query_aggregate). This forces mysql to not use the the primary index on no.

Removing the USE INDEX means that the index is used properly, and the query is orders of magnitude faster.

Apologies if this is a misconfiguration from our end, but I think that the ix_query_aggregate index should only be using when doing queries based on metadata, not when scanning the event stream.

Running: prooph/pdo-event-store: v1.12.0 prooph/event-store: v7.5.6

Thanks in advanced!