Query optimization
The optimizer is being tuned with each version, but there are still cases where the dev knows best which approach is best for a given query and MariaDB does simply a terrible job - dozens to hundreds of times slower queries than possible.
At this moment, the only way to control order of tables as shown with EXPLAIN is to use STRAIGHT_JOIN, which in some cases also forces the use of DISTINCT or GROUP BY which makes the queries slower than for example subqueries in WHERE statement without the need to use those. It also requires some statements to be completely rewritten, which might be realistically impossible with some more complicated, programmatically built queries.
So far, we experienced problems even with queries where only 3 tables were involved. Also yes, the optimizer hints in MariaDB can fix some issues, but even when it works, it is fundamentally not enough as the results are implicit, not explicit, and the resulting query plan might change at any time.
MySQL already has this https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html (Join-Order Optimizer Hints specifically are what would fix most of our issues with long running queries) to provide the needed control, which also guarantees that the query is optimal not only now, but also will be optimal even after upgrading to a version where the optimizer might change and suddenly choose a less optimal approach.
MariaDB includes mentions of similar functionality here: https://mariadb.com/kb/en/mariadb-53-optimizer-debugging/, but looks like this never made it into new stable versions?
Are there any plans to implement any similar functionality, or perhaps something like this already exists, only under a different name than MySQL uses?
i also didn't find a mention about this incompatibility between MariaDB and MySQL here https://mariadb.com/kb/en/incompatibilities-and-feature-differences-between-mariadb-11-0-and-mysql-8-/, though one might think it doen't need to be included as it is not a breaking change as the queries will still work.
Answer Answered by Michael Widenius in this comment.
I spent a lot of time to 11.0 to fix all known optimizer things. I have verified that the new optimizer with all changed plans in the mtr test suite and also with several customer queries that caused problems in earlier MariaDB versions and in almost all cases 11.0 did give a better plan.
That said, i do not think that 11.0 is perfect yet. It just a better base for the optimizer than any earlier MariaDB versions. Thanks to the changes and cleanups, it is much easier to extend and improve things in the optimizer in 11.0.
The main problem is 11.0 has had very little testing from high level users and because of that I have not got any feedback about the 11.0 optimizer performance.
We are working on extending maxscale to be able to simultaneous replicate to multiple servers, like 10.11 and 11.0 and compare result and timings. This is done partly to allow high level users to test 11.0 and provide feedback from running 11.0 in production. This will help us to find and fix any issues in 11.0.
If you, or anyone else are experiencing problems with the optimizer in 11.0 or above, please provide a test case in a Jira task and we will do our best to fix the issue ASAP.
