Fix problematic queries without touching application code
The MySQL Query Rewrite Plugin is a powerful server-side tool that allows you to intercept and modify SQL statements before the server executes them. This is invaluable when you need to optimize or correct queries from applications you cannot modify—such as third-party software or ORM-generated code—or when you need to quickly address performance issues without application deployments.
MySQL provides two types of query rewrite plugins: a pre-parse plugin (which works with the raw query string) and a post-parse plugin (which works with the parsed query tree). The Rewriter plugin included with MySQL is a post-parse plugin, making it more efficient and intelligent in its matching capabilities.
Add optimizer hints to poorly performing queries
Fix problematic query patterns, like removing unnecessary functions that prevent index usage
Schema refactoring by transparently redirecting queries during column or table name changes
Force specific join orders or query execution plans
Limit result sets automatically for queries that might return excessive data
The Query Rewrite Plugin is installed using a SQL script located in your MySQL installation's share directory. First, locate this directory and then run the installation script:
# Find the correct directory (adjust version number as needed)
cd /usr/share/mysql-8.4/
# Install the plugin
mysql -u root -p < install_rewriter.sql
The script will prompt for your MySQL root password.
After running the script, verify the plugin is active:
SHOW GLOBAL VARIABLES LIKE 'rewriter_enabled';
If installed correctly, you should see:
text
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| rewriter_enabled | ON |
+------------------+-------+
You can also check the plugin status in the information schema:
sql
SELECT plugin_name, plugin_status
FROM information_schema.plugins
WHERE plugin_name = 'Rewriter';
Running the installation script creates several database objects:
query_rewrite database: A dedicated database for rewrite functionality
rewrite_rules table: Stores all your rewrite rules
Rewriter plugin: The actual plugin that performs the rewrites
flush_rewrite_rules() stored procedure: Loads rules from the table into plugin memory
The rewrite_rules table contains all the rules for query rewriting. Here's its structure and key columns:
Column Description
id Unique identifier for each rule
pattern The query pattern to match
pattern_database Database context for unqualified table names
replacement The rewritten query
enabled Whether the rule is active ('YES' or 'NO')
message Error messages if rule loading fails
pattern_digest Hash value of the normalized pattern
normalized_pattern Digest form of the pattern
Let's create a simple rule that rewrites SELECT ? to SELECT ? + 1:
INSERT INTO query_rewrite.rewrite_rules (pattern, replacement)
VALUES ('SELECT ?', 'SELECT ? + 1');
Important: After inserting or modifying rules, you must load them into the plugin's memory:
CALL query_rewrite.flush_rewrite_rules();
A common use case is removing functions from WHERE clauses that prevent index usage. Consider this query:
UPDATE qrw8012 SET name = 'hercules7sakthi' WHERE LOWER(name) = 'sakthi';
The LOWER() function prevents using an index on the name column. Here's how to rewrite it:
INSERT INTO query_rewrite.rewrite_rules
(pattern, replacement, pattern_database)
VALUES
('UPDATE qrw8012 SET name = ? WHERE LOWER(name) = ?',
'UPDATE qrw8012 SET name = ? WHERE name = ?',
'mydb');
CALL query_rewrite.flush_rewrite_rules();
The plugin uses a sophisticated pattern matching system:
? characters act as parameter markers that match data values (not SQL keywords or identifiers)
Matching is syntax-aware, not text-based—case insensitivity and whitespace differences are ignored
The plugin computes a digest hash for quick matching
Use pattern_database to specify the database context for unqualified table names
When a query is rewritten, MySQL generates a warning. Test your rule and check for warnings:
-- Test the query
SELECT 10;
-- Check if rewriting occurred
SHOW WARNINGS;
The output will show:
+-------+------+----------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------+
| Note | 1105 | Query 'SELECT 10' rewritten to 'SELECT 10 + 1' by a query rewrite plugin |
+-------+------+----------------------------------------------------------------+
Check the plugin's status variables to monitor its activity:
SHOW GLOBAL STATUS LIKE 'Rewriter%';
Key status variables include:
Rewriter_number_loaded_rules: Number of rules currently loaded
Rewriter_number_rewritten_queries: Number of queries rewritten since startup
Rewriter_reload_error: Whether the last rule reload had errors
Examine all configured rules:
SELECT * FROM query_rewrite.rewrite_rules;
Or for a more readable view:
SELECT id, pattern_database, pattern, replacement, enabled, message
FROM query_rewrite.rewrite_rules\G
To disable a rule without deleting it:
UPDATE query_rewrite.rewrite_rules
SET enabled = 'NO'
WHERE id = 1;
CALL query_rewrite.flush_rewrite_rules();
To modify a rule:
UPDATE query_rewrite.rewrite_rules
SET replacement = 'SELECT ? + 2'
WHERE id = 1;
CALL query_rewrite.flush_rewrite_rules();
Remove a specific rule:
DELETE FROM query_rewrite.rewrite_rules
WHERE id = 1;
CALL query_rewrite.flush_rewrite_rules();
Or remove all rules:
TRUNCATE TABLE query_rewrite.rewrite_rules;
CALL query_rewrite.flush_rewrite_rules();
If you need to remove the plugin entirely:
mysql -u root -p < uninstall_rewriter.sql
Warning: This will drop all your rewrite rules permanently.
You can temporarily disable the plugin without removing it:
SET GLOBAL rewriter_enabled = OFF;
To enable it again later:
SET GLOBAL rewriter_enabled = ON;
If flush_rewrite_rules() reports an error, check the message column for details:
SELECT id, pattern, message
FROM query_rewrite.rewrite_rules
WHERE message IS NOT NULL;
Common issues include:
Syntax errors in pattern or replacement
Missing pattern_database for unqualified table names
Unsupported statement types (though the plugin now supports INSERT, UPDATE, DELETE, REPLACE beyond just SELECT)
The plugin involves some overhead even when disabled, so only install it if you plan to use it
Recent MySQL versions (5.7.14+) have significantly improved performance and reduced locking issues
Rules are cached in memory, so flushing rules is needed only after changes
Always specify pattern_database for rules with unqualified table names
Test rules thoroughly before deploying to production
Use SHOW WARNINGS to verify rewrites are working as expected
Monitor the Rewriter_number_rewritten_queries status variable to ensure your rules are being applied
Remember that statements in stored programs or views are not subject to rewriting
Users with SKIP_QUERY_REWRITE privilege can bypass query rewriting
The MySQL Query Rewrite Plugin is an exceptionally powerful tool for database administrators, providing the ability to fix query performance and correctness issues transparently. With this guide, you're equipped to install, configure, and manage query rewrites effectively in your MySQL environment.