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.
