This is a followup to
Join optimizer can be very slow when the join has many tables and join prefix pruning feature doesn't manage to prune join prefixes.
An important special case is when a subset of join tables are joined with an equi-join condition on a primary key, and so can be joined using eq_ref in any order. Then, query plans that use different permutations of these tables have the same cost. The optimizer will consider all possible permutations. This can take a lot of CPU but provide little benefit.
The fix for
MDEV-28073 addresses these issues to some extent and was pushed into 10.6. This MDEV is about more comprehensive fix.
Join prefix pruning is now controlled by @@optimizer_prune_level variable. The new pruning is done when @@optimizer_prune_level=2, which is the new default. One can disable certain aspects (TODO elaborate) of the new behavior by setting @@optimizer_prune_level=1 (the default before 10.10).
It is preview-10.10-