Using MySQL Query Rewrite Plugin: Installation, Configuration, and Performance Evaluation

MySQL added support for Query Rewrite starting from version 5.7.6, allowing SQL statements that match defined patterns to be automatically rewritten, which is especially useful for large tables or queries that perform costly COUNT(*) or GROUP operations.

Install Plugin

$ cd /opt/mysql/share
ll | grep rewrite
-rw-r--r-- 1 root root   1812 Jun 27  2018 install_rewriter.sql
-rw-r--r-- 1 root root    834 Jun 27  2018 uninstall_rewriter.sql

Run the installation script:

mysql -u root -p < install_rewriter.sql

After installation, the plugin is enabled and creates the query_rewrite database with a rewrite_rules table.

SHOW GLOBAL VARIABLES LIKE 'rewriter_enabled';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| rewriter_enabled | ON    |
+------------------+-------+

List databases and tables to verify:

show databases;
... query_rewrite ...
show tables;
+-------------------------+
| Tables_in_query_rewrite |
+-------------------------+
| rewrite_rules           |
+-------------------------+

Plugin Usage

1. Add a rewrite rule:

INSERT INTO query_rewrite.rewrite_rules (pattern, replacement)
VALUES ('SELECT ?', 'SELECT ? + 1');

2. Refresh rules into memory:

CALL query_rewrite.flush_rewrite_rules();

3. View loaded rules:

SELECT * FROM query_rewrite.rewrite_rules\G

The plugin normalizes patterns and generates a pattern digest; for example, it can rewrite SELECT COUNT(*) to SELECT COUNT(?) .

4. If a rule fails to load, an error like ERROR 1644 (45000): Loading of some rule(s) failed. appears, and the message column provides details.

5. The plugin currently supports only SELECT patterns in MySQL 5.7; support for INSERT, UPDATE, DELETE is planned for MySQL 8.0.

6. Disable or enable a rule:

UPDATE query_rewrite.rewrite_rules SET enabled='NO' WHERE id=1;
CALL query_rewrite.flush_rewrite_rules();
UPDATE query_rewrite.rewrite_rules SET enabled='YES' WHERE id=1;
CALL query_rewrite.flush_rewrite_rules();

7. Specify the database for which a rule applies using the pattern_database field.

INSERT INTO rewrite_rules(pattern,replacement,pattern_database) VALUES('select * from sbtest1 where id=1','select * from sbtest1 where id=2','sysbench');

If no database is specified, a parse error occurs.

Practical Test

A sysbench table with 10 million rows is used. A Python script measures the time of a plain SELECT COUNT(*) query (≈1.94 s). After adding a rewrite rule that returns the count directly, the same query completes in ≈0.07 s, showing a dramatic speedup.

# Python example
#!/usr/bin/env python
conn = pymysql.connect(...)
b_time = time.time()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute('select count(*) from sbtest1')
print('time cost:%s' % (time.time()-b_time))

Performance Benchmark

Using sysbench with 72 threads, the QPS and TPS values with and without the plugin are comparable, while the overall impact on the database is less than 1%.

-- Without plugin
... QPS/TPS numbers ...
-- With plugin
... QPS/TPS numbers ...

Conclusion

When adding indexes is impossible or too time‑consuming, the Query Rewrite plugin offers a fast way to rewrite costly queries, eliminate slow scans, and restore database performance with minimal overhead.

References

https://dev.mysql.com/worklog/task/?id=8776

https://dev.mysql.com/doc/refman/5.7/en/rewriter-query-rewrite-plugin-usage.html