At a recent optimizer webinar, I talked about MySQL introducing a new style for hints, and that MySQL 5.7 also added support for more hints, see Sergey Glukhov’s blog. A question I got at the end of the webinar was what to do with the hints in the application code after an upgrade?

The MySQL optimizer makes decisions on which query plan to choose based on the built-in cost model and statistics from storage engines, in addition to dictionary information. For each release, the optimizer gets smarter and smarter, the cost model becomes more advanced, statistics gets more fine grained, and with the planned histogram support, the optimizer will also get more information on data distribution.

While optimizer improves for each release, query plans a user has specified using hints in the application code remain unchanged. These hints may not be needed after an upgrade, and in the worst case, they can become counterproductive. We advise users to retest hints during upgrade and drop those that are no longer needed. This works fine if you have full control over the application code, but it doesn’t work if you don’t.

In 5.7, MySQL introduced a query rewrite plugin which can be very useful in such a scenario. Instead of adding hints directly into the application code, it allows users to rewrite a query by adding hints through the query rewrite plugin. Martin’s blog shows how to rewrite a query using this new plugin. During an upgrade, users can simply get a list of all rewrite rules from the rewrite_rules table, and then they can easily enable and disable each rewrite rule to verify if the hint is still useful. If not, the rewrite rule can be removed without changing any application code.

I hope the query rewrite plugin combined with improved hint support in MySQL 5.7 makes upgrading easier for users.