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:

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

| improve this question | | | | |
  • I don't know if anything can be done about MariaDB's behaviour regarding the number of joins but I would just rewrite the query entirely. Your query is pivoting data, and with that many columns I'd use a different pivoting technique, namely conditional aggregation. Here's an example in one of my answers: Select Multiple Values from Same Column; one sql statement and split into separate columns. I'd like to add that it would still be useful to know the answer to your question as it is. Hopefully there's an explanation and someone knows it. – Andriy M May 27 '19 at 14:12
  • Unfortunately this is not a pivot problem and this is a simple query, although it makes many join to the same table. Joins can be made to other tables and can be nested. So using a pivoting technique is not an option. The only other option is completely changing the persistence layer – SCC May 28 '19 at 16:16
  • Why would you not expect a PLAN to change on adding a JOIN? Query changes => PLAN changes! I'm giving you a plus one though for an excellently documented first question - it's by far and away one of the best I've ever seen in 5 years here! Congratulations! p.s. welcome to the forum! :-) – Vérace Jun 2 '19 at 13:42
  • Ok, I know that plan can change if query changes. Many factors can change plan, db optimizers are bad beasts to deal with. I would only like to find a way to drive this behaviour, or at least move the change forward. After all, switching from milliseconds and micro-resources to several minutes and huge resources when adding the same join and conditions on the same fields isn't a great optimization!! ;D – SCC Jun 3 '19 at 14:53

Browse other questions tagged or ask your own question.