Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.3.14, 10.3.15, 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
Windows 10 x64, MariaDB 10.3.15 with none my.ini file
CentOS 7.6.1810 x64, MariaDB 10.3.14 with default configuration
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
Issue Links
- relates to
-
MDEV-11287 Query that should result in only four rows never completes.
- Stalled
-
MDEV-24849 high optimizer_search_depth affects query performance
- Closed