Hello,
I have a form with several dbjoins elements (rendered as dropdown) which takes data from the same table, but the returned info is filtered for each element using the "Where" condition ( {thistable}.field_type = 1 and so on ). All elements are mandatory to be selected.
There is no relations between elements; i use Ajax Update to retrieve the {shortlang} placeholder value (but the problem exists even without Ajax Update). Each dropdown element should be displayed in list view.
The problem is that the list view load very slow (and there are only 16000 lines). Using debug option in Fabrik i found that using LEFT JOIN increase loading time over 2 seconds..but i don't know why and how can i rewrite the information and still keep the dbjoin elements in form:
/* Affected rows: 0 Found rows: 16,108 Warnings: 0 Duration for 1 query: 2.578 sec. (+ 0.078 sec. network) */
Testing the query but using "and" instead of left join:
/* Affected rows: 0 Found rows: 16,108 Warnings: 0 Duration for 1 query: 0.156 sec. (+ 0.062 sec. network) */
The "explain" shows that indexes are used for joined table(s).
Windows 2012 Server, Apache 2.4, PHP 7.3, 16GB RAM, Fabrik Github version updated last week
Thank you
I have a form with several dbjoins elements (rendered as dropdown) which takes data from the same table, but the returned info is filtered for each element using the "Where" condition ( {thistable}.field_type = 1 and so on ). All elements are mandatory to be selected.
There is no relations between elements; i use Ajax Update to retrieve the {shortlang} placeholder value (but the problem exists even without Ajax Update). Each dropdown element should be displayed in list view.
The problem is that the list view load very slow (and there are only 16000 lines). Using debug option in Fabrik i found that using LEFT JOIN increase loading time over 2 seconds..but i don't know why and how can i rewrite the information and still keep the dbjoin elements in form:
Code:
SELECT DISTINCT `contracts_tool`.`id` AS __pk_val FROM `contracts_tool`
LEFT JOIN `contracte_setari_739_repeat` AS `contracte_setari_739_repeat` ON `contracte_setari_739_repeat`.`parent_id` = `contracts_tool`.`tip_client`
LEFT JOIN `contracte_setari_739_repeat` AS `contracte_setari_739_repeat_0` ON `contracte_setari_739_repeat_0`.`parent_id` = `contracts_tool`.`template`
LEFT JOIN `contracte_setari_739_repeat` AS `contracte_setari_739_repeat_1` ON `contracte_setari_739_repeat_1`.`parent_id` = `contracts_tool`.`solicitare`
LEFT JOIN `contracte_setari_739_repeat` AS `contracte_setari_739_repeat_2` ON `contracte_setari_739_repeat_2`.`parent_id` = `contracts_tool`.`forma_juridica`
WHERE ( contracts_tool.limba = 'ro' )
ORDER BY `contracts_tool`.`date_time` DESC/* Affected rows: 0 Found rows: 16,108 Warnings: 0 Duration for 1 query: 2.578 sec. (+ 0.078 sec. network) */
Testing the query but using "and" instead of left join:
Code:
SELECT DISTINCT `contracts_tool`.`id` AS __pk_val FROM `contracts_tool`
LEFT JOIN `contracte_setari_739_repeat` AS `contracte_setari_739_repeat`
ON
(`contracte_setari_739_repeat`.`parent_id` = `contracts_tool`.`tip_client`
and `contracte_setari_739_repeat`.`parent_id` = `contracts_tool`.`template`
and`contracte_setari_739_repeat`.`parent_id` = `contracts_tool`.`solicitare`
and `contracte_setari_739_repeat`.`parent_id` = `contracts_tool`.`forma_juridica`)
WHERE ( contracts_tool.limba = 'ro' )
ORDER BY `contracts_tool`.`date_time` DESC/* Affected rows: 0 Found rows: 16,108 Warnings: 0 Duration for 1 query: 0.156 sec. (+ 0.062 sec. network) */
The "explain" shows that indexes are used for joined table(s).
Windows 2012 Server, Apache 2.4, PHP 7.3, 16GB RAM, Fabrik Github version updated last week
Thank you
