Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-20900

IN predicate to IN subquery conversion causes performance regression

    XMLWordPrintable

    Details

      Description

      This issue affects Magento2 (leading eCommerce CRM): https://magento.com/
      Most likely related to MDEV-12176.

      Magento2 is using Entity-Attribute-Value model (EAV), also known as object-attribute-value.
      with 315 tables. Here is the diagram:
      https://anna.voelkl.at/magento-ce-2-1-3-database-diagram/

      As a result, there are multiple "IN " statements in queries. Here is one of the problematic queries:
      SELECT `main_table`.* FROM `url_rewrite` AS `main_table` WHERE (`redirect_type` = '0') AND (`entity_type` = 'product') AND (`entity_id` IN('448', '503', '532', '547 ........... <------ if the number of products here exceeds 1000, then "optimizer" creates subqueries.

      In MariaDB 10.1 and 10.2 and also MySQL 8 it's a SIMPLE execution plan that takes 6 seconds:

      • see screenshot 1

      95210 rows in set (5.58 sec)

      On MariaDB 10.3 and 10.4 on the same server with the same settings (including optimizer_switch) it's now 35 mins:

      • see screenshot 2

      However, there is no way to disable subqueries in MDEV-12176 (@@in_predicate_conversion_threshold doesn't work on production releases)

      The database is about 7GB and contains sensitive data. That's why I didn't attach it to this issue. It is available on request.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              varun Varun Gupta
              Reporter:
              babanski Alex Babanski
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: