Sackgesicht VIP
Total posts: 1,636
12 Янв 2013 17:11

On a SECTION with a TYPE (25 fields) the display of the intro view including 2 relation fields is normally ~ 1sec (according to Joomla debug info).

Now it went up to 2.8 sec !!!! --> almost 3 times longer

After investigating i found the main reason for this is the "Filter box shows nums" setting.

SELECT p.*,(SELECT COUNT(id) 

  FROM xxxx_js_res_field_multilevelselect as n 

  WHERE n.parent_id = p.id) as childs_num,(SELECT count(*) 

  FROM `xxxx_js_res_record_values` 

  WHERE field_key = 'k0362d23b33c4bf360668cbc9a59e7d96' 

  AND field_value = p.id) as record_num 

  FROM xxxx_js_res_field_multilevelselect p 

  WHERE p.field_id = '82' 

  AND p.parent_id = '1' 

  AND p.level = '1' 

  ORDER BY name ASC

The query takes in phpmyadmin around 0.62 sec. (In Navicat ~ 1.35 sec)

Just changing the order of conditions in the where clause to

WHERE field_value = p.id 

 AND field_key = 'k0362d23b33c4bf360668cbc9a59e7d96') as record_num 

brings it down to 0.54 sec (Navicat 1.19 sec)

Introducing a new index in js_res_record_values on field_id and using it instead of the field_key brings it down to 0.41 (Navicat 0.93)

But i believe a better approach is to query the js_res_record_values when we want to get the number of existing records.

I used this query:

SELECT count(id),

    (SELECT b.`name`

    FROM `xxx_js_res_field_multilevelselect` AS b

    WHERE b.id = a.field_value

    ) as name

FROM `xxx_js_res_record_values` AS a

WHERE `field_id` = '82'

AND `value_index` = 0

GROUP BY field_value

ORDER BY name

and the result is now 0.05 sec (Navicat 0.06 sec)

after removing the index and using field_key, it is almost the same --> 0.06 sec( navicat 0.07 sec)

Maybe you can consider the new query for the counting since it is 10 times faster.

Последние изменения: 02 Март 2014


Sackgesicht VIP
Total posts: 1,636
12 Янв 2013 21:32

there is another issue with parameter "Show number of children" .. it is not working, it always shows children number.

In multilevelselect.php line 336 the $item->childs_num needs to be replaced with $this->params->get('params.childs_num'

I would suggest to choose between 2 different queries depending on the 2 "number parameter" in the backend.

query 1 is existing query (just with the removal of record_nums ) and query 2 is the proposed query from above.

query 2 will only be used if the record number needs to be displayed. No need for the child_nums here, it would only increase the query time to 0.18 sec (3 times).


Sergey
Total posts: 13,748
13 Янв 2013 00:41

Thank you Sack (as some people call you here :))) for very extensive and valuable investigation. You know I like to make everything working quicker :)

But i believe a better approach is to query the js_res_record_values when we want to get the number of existing records.

I think there is parameter in filter group which turn counting records numbering off. Also I made it so that if numbering is on it is separate query.

But i believe a better approach is to query the js_res_record_values when we want to get the number of existing records.

there is another issue with parameter "Show number of children" .. it is not working, it always shows children number.

In multilevelselect.php line 336 the $item->childs_num needs to be replaced with $this->params->get('params.childs_num'

Fixed. This parameter will work only for input form not for filter because it is not needed and not used there. And it is working with ajax so it would not affect speed.

Работает на Cobalt