I created a statistic page for one of my TYPES. The first attempt took ~10 sec to finish 80+ queries. After optimizing them, it went down to around 2.3 sec, which is an acceptable time for the generated information.
A deeper look revealed a small bottle neck --> the field_key index in js_res_record_values.
After adding an index on field_id and changing some queries a little, i get the whole page in 0.6 sec now.
This happens with a 2,000 record type with 59,300 record values.
My next Statistic page will process another TYPE with 54K records and 159K record values. My initial 1 query alone, which gets all information, takes around 39 sec.
(In fairness -- optimized for field_key index, it takes ~16.5 sec)
After adding the field_id index, it went down to 11 sec (optimized for field_id). I am sure, by dividing it into several individual queries, it will end up at 1/3 of the time (but only with the additional field_id index).
Maybe you can consider adding this index by default to Cobalt8.
Idea
Announcement
Conversation
Praise
Problem
Question
Open
I created a statistic page for one of my TYPES. The first attempt took ~10 sec to finish 80+ queries. After optimizing them, it went down to around 2.3 sec, which is an acceptable time for the generated information.
A deeper look revealed a small bottle neck --> the field_key index in js_res_record_values.
After adding an index on field_id and changing some queries a little, i get the whole page in 0.6 sec now.
This happens with a 2,000 record type with 59,300 record values.
My next Statistic page will process another TYPE with 54K records and 159K record values. My initial 1 query alone, which gets all information, takes around 39 sec.
(In fairness -- optimized for field_key index, it takes ~16.5 sec)
After adding the field_id index, it went down to 11 sec (optimized for field_id). I am sure, by dividing it into several individual queries, it will end up at 1/3 of the time (but only with the additional field_id index).
Maybe you can consider adding this index by default to Cobalt8.