On complex optimizations and optimizer hints

In an ideal world, you should never need to use optimizer hints. In the real world, hints are necessary. We needed hints even before we’ve made a release. The first requests came from our Quality engineer who complained about it being difficult to hit particular join orders while using a particular subquery strategy.

So, why not add the needed hints? MySQL already has the STRAIGHT_JOIN and FORCE INDEX, you can add more as required. The problem is that once you have transformations that change the query to be far enough from the original SQL, you don’t have a “natural” place or syntax for hints anymore.

For example, if you have a join

SELECT ... FROM table1, table2, table3 WHERE ....

and want a join order of (table2, table3, table1), you can write it as

SELECT ... FROM table2 STRAIGHT_JOIN table3 STRAIGHT_JOIN table1

and that gives the optimizer the join order while still looking like SQL. Now, how do you force the same join order when the query looks like this:

SELECT ...
FROM table1
WHERE
  table1.column IN (SELECT table2.col1
                    FROM table2
                    WHERE
                      table2.col2 IN (SELECT table3.col3
                                      FROM table3)

The obvious answers are

1. you have to put table2, table3, table1 together somewhere
2. your hint syntax won’t be SQL

And since you now need to invent the whole new non-SQL syntax to specify query plans, the task becomes just too hard. It’s difficult to design a future-proof compact syntax for specifying query plans. My not-really-solution to the problem was to

• Add a @@debug_optimizer_prefer_join_prefix system variable. That way, I avoided messing with the parser
• Do it in a never-to-be-pushed branch of MariaDB, so this solution is not exposed to the same rigor as everything that goes into release

As a result, you can make the optimizer to “strongly prefer” some particular join prefixes. The preferences are applied to each select, though. If you have a UNION and want to have one preference for on branch of it, and another preference for the other, there is no way to achieve that.

Given this kind of limitations, I think the patch is useful for development or debugging, but not for production use. Everything (documentation, link to launchpad tree) are here, at the mariadb-53-optimizer-debugging page.