Details

      Description

      Criterions should avoid using uncached load functions and sub selects.

      See this PR for info: https://github.com/ezsystems/ezp-next/pull/132

        Issue Links

          Activity

          Hide
          Gaetano Giunta (Inactive) added a comment -

          It would be interesting the different query plans generated by different versions of mysql for subselects.

          For eaxmple I have seen the following query recently, which might be optimized by turning subqueries into joins:

          SELECT COUNT
          FROM `ezcontentobject`
          INNER JOIN ezcontentobject_version ON ezcontentobject.id = ezcontentobject_version.contentobject_id
          LEFT JOIN `ezcontentobject_tree` AS `53046a8abff53` ON `53046a8abff53`.`contentobject_id` = `ezcontentobject`.`id`
          WHERE (
          (

          `ezcontentobject`.`id` IN (
          SELECT `contentobject_id`
          FROM `ezcontentobject_attribute`
          WHERE (
          `ezcontentobject_attribute`.`version` = `ezcontentobject`.`current_version`
          AND (
          `contentclassattribute_id` IN ('4', '6', '116', '146', '159', '345', '430', '432', '433', '435', '443', '447', '450', '463')
          AND `sort_key_string` = 'produkter'
          )
          )
          )
          AND
          `ezcontentobject`.`contentclass_id` IN (
          SELECT `id`
          FROM `ezcontentclass`
          WHERE
          `identifier` IN ('folder')
          )
          AND
          `53046a8abff53`.`depth` BETWEEN '2' AND '3'
          AND
          `ezcontentobject`.`id` IN (
          SELECT `contentobject_id`
          FROM `ezcontentobject_tree`
          WHERE (
          `ezcontentobject_tree`.`is_hidden` = 0
          AND
          `ezcontentobject_tree`.`is_invisible` = 0
          )
          )
          AND
          (`ezcontentobject`.`section_id` IN ('1', '6', '7') OR `ezcontentobject`.`contentclass_id` IN ('12') OR `ezcontentobject`.`contentclass_id` IN ('5', '67'))
          )
          AND ezcontentobject_version.status = 1
          );

          Show
          Gaetano Giunta (Inactive) added a comment - It would be interesting the different query plans generated by different versions of mysql for subselects. For eaxmple I have seen the following query recently, which might be optimized by turning subqueries into joins: SELECT COUNT FROM `ezcontentobject` INNER JOIN ezcontentobject_version ON ezcontentobject.id = ezcontentobject_version.contentobject_id LEFT JOIN `ezcontentobject_tree` AS `53046a8abff53` ON `53046a8abff53`.`contentobject_id` = `ezcontentobject`.`id` WHERE ( ( `ezcontentobject`.`id` IN ( SELECT `contentobject_id` FROM `ezcontentobject_attribute` WHERE ( `ezcontentobject_attribute`.`version` = `ezcontentobject`.`current_version` AND ( `contentclassattribute_id` IN ('4', '6', '116', '146', '159', '345', '430', '432', '433', '435', '443', '447', '450', '463') AND `sort_key_string` = 'produkter' ) ) ) AND `ezcontentobject`.`contentclass_id` IN ( SELECT `id` FROM `ezcontentclass` WHERE `identifier` IN ('folder') ) AND `53046a8abff53`.`depth` BETWEEN '2' AND '3' AND `ezcontentobject`.`id` IN ( SELECT `contentobject_id` FROM `ezcontentobject_tree` WHERE ( `ezcontentobject_tree`.`is_hidden` = 0 AND `ezcontentobject_tree`.`is_invisible` = 0 ) ) AND (`ezcontentobject`.`section_id` IN ('1', '6', '7') OR `ezcontentobject`.`contentclass_id` IN ('12') OR `ezcontentobject`.`contentclass_id` IN ('5', '67')) ) AND ezcontentobject_version.status = 1 );
          Hide
          André Rømcke added a comment - - edited

          Yep, this is as reported in the issue.

          However it seems MariaDB does not have issues with the use of sub-selects, which would hint next version of MySQL won't either, so complicating the storage engine to get it to use joins instead is currently not prioritized and instead we currently aim to support MariaDB.

          But any idea how Postgres/Oracle handles sub selects? Able to optimize them like MariaDB?

          Show
          André Rømcke added a comment - - edited Yep, this is as reported in the issue. However it seems MariaDB does not have issues with the use of sub-selects, which would hint next version of MySQL won't either, so complicating the storage engine to get it to use joins instead is currently not prioritized and instead we currently aim to support MariaDB. But any idea how Postgres/Oracle handles sub selects? Able to optimize them like MariaDB?
          Hide
          Gaetano Giunta (Inactive) added a comment -

          I'd bet that both fare better than mariadb in that regard (mysql was notoriously the lousy one wrt subselects).

          OT: is mariadb a supported/recommended db now?
          I am not sure that its adoption will be very fast, even if it is made the default choice in RHEL...

          Show
          Gaetano Giunta (Inactive) added a comment - I'd bet that both fare better than mariadb in that regard (mysql was notoriously the lousy one wrt subselects). OT: is mariadb a supported/recommended db now? I am not sure that its adoption will be very fast, even if it is made the default choice in RHEL...
          Hide
          Gaetano Giunta (Inactive) added a comment -

          ps: while I would like to overcomplicate the criterion => sql generation, I think that the way it is built is currently simplistic.
          It should adopt more standard "compiler-passes" techniques to allow different kinds of optimizations.

          Show
          Gaetano Giunta (Inactive) added a comment - ps: while I would like to overcomplicate the criterion => sql generation, I think that the way it is built is currently simplistic. It should adopt more standard "compiler-passes" techniques to allow different kinds of optimizations.

            People

            • Assignee:
              Unassigned
              Reporter:
              André Rømcke
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: