I found MariaDB behaviour changing according to the number of JOINs in a query.
In this particular application, when the number of JOINs goes from 30 to 31 the execution plan changes, causing potential problems to the execution time and resources management.
This behaviour doesn't depend on the number of records, but when they are millions, the chosen plan considerably slows down execution using gigabytes of temporary files.
I couldn't find any configuration parameters that drive this behaviour (32 entities in JOIN is a fixed limit?), I don't know if it can be changed.
The test was made on MariaDB 10.2.x, 10.3.x and 10.4.5 RC.
Scripts to reproduce behaviour:
- Data definition (with few sample records)
- Query definition (the 31th JOIN is commented)
- Explain with 30 JOIN
- Explain with 31 JOIN
I would like to find out if there are any configuration (or system) parameters that can modify this behaviour, maybe moving the limit forward.
Any help would be greatly appreciated! Thank you