A Scenario Every DBA Has Faced
Picture this: 2 AM, an alert fires — production database is crawling, CPU at 100%, hundreds of requests timing out. You open the slow query log and the culprit jumps right out — a SELECT running a full table scan on a 50-million-row table, no index, being called 500 times per second from the application.
The dev team is asleep. The codebase is frozen ahead of tomorrow morning’s release. The CI/CD pipeline takes 30 minutes to deploy. You don’t have 30 minutes — you need a fix right now.
MySQL Query Rewrite Plugin was built for exactly these moments. I’ve used it to handle situations like this many times without touching a single line of application code.
What the Query Rewrite Plugin Is and How It Works
The Query Rewrite Plugin ships with MySQL 5.7+ (requires manual installation) and MySQL 8.0+. It hooks in at the parser layer — before a query is executed, the plugin checks whether it matches any rule defined in the query_rewrite.rewrite_rules table. If it matches, the query is replaced with the rewritten version.
There are two types of rewrites:
- Pre-parse rewrite (the
Rewriterplugin): Operates after parsing, uses pattern matching with?as a wildcard for literal values. - Post-parse rewrite: Less common, operates on the AST after parsing.
In practice, the Rewriter plugin (pre-parse) is what you’ll reach for in the vast majority of cases.
Limitations to Know Up Front
- Only rewrites
SELECT,INSERT,UPDATE,DELETE— DDL statements are not rewritten. - Pattern matching is based on query structure, not raw strings. Two queries with the same intent but written differently require two separate rules.
- Does not apply to prepared statements over the binary protocol — only works with the text protocol.
Installing and Enabling the Plugin
On MySQL 8.0, the installation script is already bundled:
# Run the installation script included with MySQL
mysql -u root -p < /usr/share/mysql/install_rewriter.sql
# Verify the plugin is active
mysql -u root -p -e "SHOW PLUGINS\G" | grep -i rewriterIf successful, the output will look like this:
Name: Rewriter
Status: ACTIVE
Type: AUDIT
Library: rewriter.soThe plugin creates a query_rewrite database with a rewrite_rules table — where you define all your rules:
DESCRIBE query_rewrite.rewrite_rules;+------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| pattern | longtext | NO | | NULL | |
| pattern_database | varchar(64) | YES | | NULL | |
| replacement | longtext | NO | | NULL | |
| enabled | enum('YES', | NO | | YES | |
| message | varchar(128) | YES | | NULL | |
| pattern_digest | varchar(64) | YES | | NULL | |
| normalized_patte | varchar(100) | YES | | NULL | |
+------------------+--------------+------+-----+---------+----------------+Hands-On: Real-World Use Cases
Use Case 1: Automatically Add LIMIT to Unbounded Queries
A classic mistake. A developer forgets LIMIT, and production pulls the entire table — I once saw a query like this return 2.3 million rows to the frontend in a single request:
INSERT INTO query_rewrite.rewrite_rules (pattern, replacement) VALUES (
'SELECT * FROM orders WHERE status = ?',
'SELECT * FROM orders WHERE status = ? LIMIT 1000'
);
-- After every INSERT/UPDATE to a rule, flush to apply it:
CALL query_rewrite.flush_rewrite_rules();From this point on, any query matching SELECT * FROM orders WHERE status = 'pending' will automatically have LIMIT 1000 appended. The application is none the wiser.
Use Case 2: Force Index Usage
The MySQL optimizer occasionally picks a bad execution plan — especially with tables that have skewed data distributions. Before reaching for the Query Rewrite Plugin, it’s worth using EXPLAIN to diagnose exactly which index MySQL is choosing and why. Once you understand the problem, rewriting directly at the database layer is your fastest lever:
INSERT INTO query_rewrite.rewrite_rules (pattern, replacement, pattern_database) VALUES (
'SELECT id, email, created_at FROM users WHERE created_at > ?',
'SELECT id, email, created_at FROM users USE INDEX (idx_created_at) WHERE created_at > ?',
'myapp'
);
CALL query_rewrite.flush_rewrite_rules();Pay attention to the pattern_database field. Leaving it NULL means the rule applies to all databases — convenient on paper, but prone to unexpected side effects. Always specify the target database explicitly.
Use Case 3: Redirect Queries to an Archive Table
Say you’ve migrated old data to orders_archive but the code still queries orders. Rather than updating the code, redirect at the database level:
INSERT INTO query_rewrite.rewrite_rules (pattern, replacement, pattern_database) VALUES (
'SELECT ? FROM orders WHERE created_at < ?',
'SELECT ? FROM orders_archive WHERE created_at < ?',
'myapp'
);
CALL query_rewrite.flush_rewrite_rules();Use Case 4: Temporarily Block a Dangerous Query
I once dealt with a database corruption incident at 3 AM that required a restore from backup — nearly two hours of downtime. The lesson I took away: block queries that can cause damage before they run, don’t wait to clean up the aftermath. The plugin can return an empty result set immediately:
-- Replace with a query that returns an empty result set
INSERT INTO query_rewrite.rewrite_rules (pattern, replacement, pattern_database) VALUES (
'SELECT * FROM users',
'SELECT * FROM users LIMIT 0',
'myapp'
);
CALL query_rewrite.flush_rewrite_rules();Verifying That a Rule Is Working
-- View all rules and their status
SELECT id, pattern, replacement, enabled, message
FROM query_rewrite.rewrite_rules;
-- If the 'message' column has a value after flushing,
-- the rule has a syntax error or is otherwise invalid
SELECT id, message FROM query_rewrite.rewrite_rules
WHERE message IS NOT NULL;After running a query from the application, verify it immediately with:
-- Show warnings from the last executed query
SHOW WARNINGS;
-- Output if the query was rewritten:
-- Level: Note
-- Code: 1105
-- Message: Query 'SELECT * FROM orders WHERE status = 'pending'' rewritten to
-- 'SELECT * FROM orders WHERE status = 'pending' LIMIT 1000' by a query rewrite pluginDisabling or Deleting a Rule
-- Temporarily disable a rule
UPDATE query_rewrite.rewrite_rules SET enabled = 'NO' WHERE id = 1;
CALL query_rewrite.flush_rewrite_rules();
-- Permanently delete a rule
DELETE FROM query_rewrite.rewrite_rules WHERE id = 1;
CALL query_rewrite.flush_rewrite_rules();Monitoring: Knowing When Rules Are Being Applied
MySQL provides built-in status variables for tracking rewrite activity:
SHOW STATUS LIKE 'Rewriter%';+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Rewriter_number_loaded_rules | 3 |
| Rewriter_number_rewritten_queries | 1547 |
| Rewriter_number_unrecognized_hints| 0 |
| Rewriter_reload_error | OFF |
+-----------------------------------+-------+If Rewriter_number_rewritten_queries keeps climbing — your rule is firing correctly. If it stays flat even though you’re sure the query ran, the pattern isn’t matching. The most common culprits are extra whitespace, different column ordering, or aliases.
Debugging a Pattern That Won’t Match
A trick I rely on: temporarily enable the general log to see the exact query text MySQL is receiving:
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/tmp/mysql_general.log';
-- Run a few queries from the application...
SET GLOBAL general_log = 'OFF';tail -f /tmp/mysql_general.log | grep "Query"Compare the query text in the log against the pattern you defined. The mismatch is almost always hiding in a spot that looks identical at first glance.
When to Use It — and When Not To
Good use cases:
- You need an immediate production hotfix with no time to deploy code.
- You’re working with a third-party application whose source code you can’t modify.
- A/B testing query optimizations without changing the application.
- Gradual migrations: redirecting queries from an old table to a new one while running both in parallel.
When to avoid it:
- Treating it as a permanent fix — rewrite rules are a temporary measure, not a substitute for fixing the code properly. For lasting gains, pair this with deeper work like tuning the buffer pool and thread pool at the server level.
- Complex query logic where a rewrite could change the semantics.
- Multi-team environments where rules aren’t documented — this is the root cause of many baffling debugging sessions down the line.
Conclusion
I’ve used the Query Rewrite Plugin to patch production more times than I can count. Each time it saved at least 30 minutes of deployment overhead — sometimes an entire on-call shift. For DBAs and DevOps engineers, this is one of those underappreciated tools that’s worth 30 minutes of your time to truly understand.
Just remember one rule of thumb: every rewrite rule must be documented — either as a comment on the rule itself or in your internal wiki. Nothing is worse than another team three months later tearing their hair out because queries from the code are returning completely different results from what they wrote.
