Multi Level Select - Performance issues

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.