Query Rewrite

MySQL supports the ability to rewrite statements on the server side. You can think of this as similar to a server-side regular expression to be able to find statements that match a certain pattern and rewrite them into a new pattern.

One of the design goals of this feature was to allow DBAs to be able to insert a query hint into a statement. This provides relief in the case that the application itself cannot be modified, such as the case where an ORM is used, or the application is proprietary.

It should be noted that while I described query rewrite as similar to a regular expression, it is far more efficient internally. As statements are parsed, the digest (also known as the prepared statement form) is checked against an internal hash table of statements that should be rewritten. If a statement is determined to need rewriting, the server will perform this step and then reparse the query. This means that queries that do not need rewriting have little overhead, and queries that need rewriting simply need to be parsed twice.

Example 33: Changing a query server side with query rewrite:

# From the command line, install query rewrite
mysql -u root -p < install_rewriter.sql

# From MySQL add a rule and flush rewrite rules
INSERT INTO query_rewrite.rewrite_rules(pattern_database, pattern, replacement) VALUES (
"world",
"SELECT * FROM Country WHERE population > ? AND continent=?",
"SELECT * FROM Country WHERE population > ? AND continent=? LIMIT 1"
);
CALL query_rewrite.flush_rewrite_rules();

 # Query rewrite triggers a warning every time a rewrite event occurs
SELECT * FROM Country WHERE population > 5000000 AND continent='Asia';
SHOW WARNINGS;
Query 'SELECT * FROM Country WHERE population > 5000000 AND continent='Asia'' rewritten to 'SELECT * FROM Country WHERE population > 5000000 AND continent='Asia' LIMIT 1' by a query rewrite plugin

SELECT * FROM query_rewrite.rewrite_rules\G
*************************** 1. row ***************************
                id: 2
           pattern: SELECT * FROM Country WHERE population > ? AND continent=?
  pattern_database: world
       replacement: SELECT * FROM Country WHERE population > ? AND continent=? LIMIT 1
           enabled: YES
           message: NULL
    pattern_digest: 88876bbb502cef6efddcc661cce77deb
normalized_pattern: select `*` from `world`.`Country` where ((`population` > ?) and (`continent` = ?))

Tip

The MySQL Server supports Query Rewrite plugins. The example shown here is a post-parse query rewrite plugin (called Rewriter) that is included with the MySQL server distribution. Certain queries may require a pre-parse plugin, which is also supported. For more information see the MySQL manual: https://dev.mysql.com/doc/refman/5.7/en/rewriter-query-rewrite-plugin.html