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

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

                Dates

                Created:
                Updated:
                Resolved: