1. Fabrik 3.9 has been released. If you have updated Joomla to 3.9, this is a required update.
    Dismiss Notice

Slow query using multiple dbjoin elements

Discussion in 'Community' started by mirceat, Jan 15, 2020.

  1. mirceat

    mirceat Member

    Level: Community
    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:

    Code (Text):
    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 (Text):
    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
     
  2. troester

    troester Well-Known Member Staff Member

    Level: Community
    So you didn't select id[recommended] as dbjoin values?
    Make sure all your columns shown in the joins (`contracte_setari_739_repeat`.`parent_id`, `contracts_tool`.`xy`) have indexes set.
     
  3. mirceat

    mirceat Member

    Level: Community
    Thank you, the indexes are set to parent_id and "xy" elements. All elements are set to INT 11, the "explain" query shows that the indexes are used..
     
  4. mirceat

    mirceat Member

    Level: Community
    Hello again,

    @troester, following your question "So you didn't select id[recommended] as dbjoin values?" i tried to change the join key to point to "id" instead of "parent_id"..and that solved the problem with loading time..

    But i need to know if i can to anything to keep parent_id as joined key and also decrease the loading time. I'm asking because it would be a major task to switch from parent_id to id..
     
  5. troester

    troester Well-Known Member Staff Member

    Level: Community
    Does parent_id really has an index?
     
  6. mirceat

    mirceat Member

    Level: Community
    Yes, it has: fb_parent_fk_parent_id_INDEX. I think is applied by Fabrik by default for each table like contracte_setari_739_repeat..
     
    Last edited: Jan 16, 2020

Share This Page