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

Query with subqueries hangs for long time with default optimizer_search_depth

    XMLWordPrintable

Details

    Description

      We have found a difference in subquery/join optimizer between MySQL 5.x and MariaDB 10.3.x. When we run a simple select query with several subqueries on MariaDB with the default value of configuration "optimizer_search_depth" (62), the query is executing for very long time, hangs in "statistics" state, with quadratic or exponencional growing of the execution time. The same query performs on MySQL 5 during a few milliseconds. We have prepared test suite. The "prepare.sql" prepares test environment a the "test.sql" demonstrates this problem. In test.sql, the first 10 subqueries are uncommented. Query in this form takes arround 6 seconds, but when we add 11. subquery, query takes 112 second already. When we have changed "optimizer_search_depth" to 1, the execution of same query was done immediately. Same result we got on Windows and Linux, in version 10.3.14 and 10.3.15

      Attachments

        1. prepare.sql
          0.8 kB
        2. test.sql
          2 kB

        Issue Links

          Activity

            People

              igor Igor Babaev
              vizus.tomas Tom Dolezal
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.