Sackgesicht VIP
Total posts: 1,636
28 Jan 2013 05:40

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.

Last Modified: 02 Mar 2014


Sergey
Total posts: 13,748
01 Feb 2013 01:07

You are right and thank you for keeping to tell me this.

Unfortunately right now even if I add field_id index, it won't change anything because in Cobalt i use only field_key for multi-type support. But I am now more confident to change field key from md2 to int format. But i think I'll do it only in Cobalt 9. Then we can improve speed without sacrifice functionality.


Sackgesicht VIP
Total posts: 1,636
01 Feb 2013 03:02

Yes i understand .. You should not even change anything, since the field_key supports the multitype .. I added the index manually for my own modules. Thats quite ok.

Powered by Cobalt