Slow aggregate load performance for SingleStreamStrategy

[fritz-gerneth]

Not sure yet why / what is happening here exactly. This is happening on a table with ~500k recorded events in a SingleStreamStrategy. The original query takes about 2 seconds for us to load. The second query takes 70ms to load. This was live-tested with a modified MySqlEventStore to use the second query on a MySql 5.7 & MySQL 8 instance. Loading all aggregates one after another caused the server to have 90% CPU utliziation

Anyone got any ideas what might be the cause here? I mean the fix would be relatively simple - let the Storage strategy decide how they want to match metadata... but I'd like to understand the underlying cause here first..

EXPLAIN 
   SELECT * FROM `_my_stream`  WHERE 
       JSON_UNQUOTE(metadata->"$._aggregate_id") = "ff"  
       AND JSON_UNQUOTE(metadata->"$._aggregate_version") > 3  AND `no` >= 5   
       AND JSON_UNQUOTE(metadata->"$._aggregate_type") = "dffg"  ORDER BY `no` ASC IMIT 1000;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	_my_stream		ref	PRIMARY,ix_unique_event,ix_query_aggregate	ix_unique_event	754	const,const	1	50.0	Using index condition; Using where; Using filesort

EXPLAIN 
   SELECT * FROM `_my_stream`  WHERE 
       aggregate_id = "ff"  
       AND aggregate_version > 3  AND `no` >= 5   
       AND aggregate_type = "dffg"  ORDER BY `no` ASC IMIT 1000;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	_my_stream		ref	PRIMARY,ix_unique_event,ix_query_aggregate	ix_query_aggregate	754	const,const	1	16.66	Using index condition; Using where

[fritz-gerneth]

I only noticed now our custom implementation of the storage strategy does not force the index (everything else is the same). With the forced index, performance gets even worse (~5sec/query).

EXPLAIN 
   SELECT * FROM `_my_stream` igo USE INDEX(`ix_query_aggregate`) 
   	   WHERE JSON_UNQUOTE(metadata->"$._aggregate_id") = "ff"  
       AND JSON_UNQUOTE(metadata->"$._aggregate_version") > 3 
       AND `no` >= 5   
       AND JSON_UNQUOTE(metadata->"$._aggregate_type") = "dffg"  
       ORDER BY `no` ASC LIMIT 1000;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	_my_stream		range	ix_query_aggregate	ix_query_aggregate	762		1	100.0	Using index condition; Using where

[codeliner]

@sandrokeil do you have an idea?

[sandrokeil]

The original query takes about 2 seconds for us to load. The second query takes 70ms to load.

Maybe it depends on the query cache configuration. Something like this.

[fritz-gerneth]

We have the default query cache configuration. I'll try the original version with your configuration when I am back.