MySQL 8 Query Rewriter Plugins and DDL Rewriter

Optimizing MySQL 8 performance with Query Rewriter Plugins and DDL Rewriter 

When you are building a Database Systems Infrastructure for performance of an WebScale application, the optimal query performance is very important for both customer satisfaction and efficient usage of systems infrastructure. There are several ways to tune MySQL for performance, This can be done on configuration level, efficient indexing, optimal distribution of disk I/O etc. But, if your query is expensive then tuning MySQL will not benefit much and you may eventually invest on very expensive hardware for performance.  So application tuning cannot be supplemented either with generous MySQL system variables sizing or infrastructure investments / upgrades, This gets more complicated if you have ORM where you have no control on the application source. In such situations MySQL 8 Query Rewrite comes as a great savior with the capability to rewrite queries on MySQL Server, Just like how regular expression find statements that match a defined pattern and rewrite. When you very closely examine MySQL 8 query rewrite plugin, It is like a feature to automatically insert hint to the query for performance.  In this blog post I have explained how to install, configure and use MySQL Query Rewriter Plugin.

» Query Rewriter Plugin installation

There are two files involved to install (install_rewriter.sql) and uninstall (uninstall_rewriter.sql) Query Rewriter Plugin, You can find their location by querying MySQL system variable lc_messages_dir :

» Install Query Rewriter Plugin

The install_rewriter.sql will create schema query_rewrite and table rewrite_rules 

» MySQL 8 Query Rewriter Plugin test case

I have created table qryrrt with some data for testing MySQL 8 Query Rewriter Plugin

Let’s see now MySQL 8 Query Rewriter Plugin in action:

Step 1: Update rewrite_rules table in database query_rewrite with SQL pattern and replacement for the same query

Step 2: Call the function query_rewrite.flush_rewrite_rules()  


If you ever wrongly configure rewrite_rules table, You will get the error message ERROR 1644 (45000): Loading of some rule(s) failed during the flush_rewrite_rules function call and we can also check warning message to confirm if the query rules are applied or not

Testing MySQL 8 Query Rewriter Plugin

From the above example we can confirm MySQL 8 Query Rewriter Plugin is working as expected

How to uninstall MySQL 8 Query Rewriter Plugin ?

To uninstall the MySQL 8 Query Rewriter  Plugin you have to load the SQL file “uninstall_rewriter.sql”. It will drop the database, function, and uninstall the plugin as well.

DDL Rewriter Plugin

MySQL 8.0.16 introduced ddl_rewriter plugin to rewrite the CREATE TABLE statements received by the MySQL Server by removing ENCRYPTION, DATA DIRECTORY and INDEX DIRECTORY clauses, All this happens before server parses and executes them. MySQL DDL Rewriter is very helpful while restoring tables from SQL dump files created from databases that are encrypted or those having tables stored outside the data directory.

Installing DDL Rewriter Plugin:

Let’s understand how DDL Rewriter Plugin works by creating table ddl_rewrite

In the example above 👆, ddl_rewriter plugin has removed both encryption and data/index directories of create table SQL command (warning confirms the same) and this feature is really useful when doing complex database structure migration.


MySQL infrastructure operations productivity tools simplifies the lives of DBAs and Database SREs, I am sure MySQL 8 Query Rewriter Plugins and DDL Rewriter will improve a lot for good in future releases of MySQL, Thanks for your comments and feedbacks.

About Shiv Iyer 456 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.