I am working on migration from SQL server to Memsql and as memsql force us to make shard key on pk column so we are not able to make shard key in different mediums cardinality column and 80% queries has where clause on column which is not a pk and due to that some procedure is taking more than 1 mins to execute.(tried index as well but not improving performance much)
I proposed fix to create temporary table with shard key of medium cardinality column within the proc and storing records in this table using select insert and will use this temp table instead of main table in procedure but I have some queries regrading memory management:-
1- what would happen if leaf node goes down?
2:- when garbage collector will flush data from temp table and free the memory??
Thanks in advance