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
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
- you have to put
table2, table3, table1together somewhere
- 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_prefixsystem 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.
One way to specify join order for a specific subquery, would be to use the fact that all subqueries are enumerated. This allows to extend the prefix syntax to:
“(query_id_1, t_i1, t_i2, …), (query_id_2, t_j1, t_j2), …”
Applicable both for subqueries in any clause and UNIONS.