Query Rewriting in ProxySQL
ProxySQL can modify SQL queries on the fly before sending them to MySQL backends using the replace_pattern column in mysql_query_rules. This is done using regular expressions — ProxySQL finds the match_pattern and replaces it with replace_pattern.
Query rewriting is extremely useful for:
- Adding query hints without touching application code
- Renaming tables/schemas during migration
- Adding or removing SQL directives (SQL_NO_CACHE, STRAIGHT_JOIN)
- Fixing poorly written queries from legacy applications
- Adding LIMIT clauses to runaway queries
Basic Rewriting Examples
Add SQL_NO_CACHE to all SELECTs
SQL — Add SQL_NO_CACHE
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, replace_pattern, apply, comment)
VALUES
(300, 1, '^(SELECT)', '\1 SQL_NO_CACHE', 0, 'Add SQL_NO_CACHE hint');
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
-- Before: SELECT * FROM users WHERE id=1
-- After: SELECT SQL_NO_CACHE * FROM users WHERE id=1Add LIMIT to prevent runaway queries
SQL — Add LIMIT Cap
-- Add LIMIT 10000 to any SELECT without a LIMIT clause
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, replace_pattern, apply, comment)
VALUES
(301, 1, '^(SELECT (?!.*LIMIT).*)', '\1 LIMIT 10000', 1, 'Cap SELECTs at 10000 rows');
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;Table rename during migration
SQL — Table Rename
-- Transparently redirect queries from old_users to new_users
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, replace_pattern, apply, comment)
VALUES
(302, 1, 'old_users', 'new_users', 0, 'Rename old_users → new_users');
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
-- Before: SELECT * FROM old_users WHERE email='a@b.com'
-- After: SELECT * FROM new_users WHERE email='a@b.com'Add index hint to specific query
SQL — Add Index Hint
-- Force index usage for a slow query
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, replace_pattern, apply, comment)
VALUES
(303, 1, 'SELECT (.*) FROM orders WHERE status',
'SELECT \1 FROM orders USE INDEX (idx_status) WHERE status',
1, 'Force index on orders status queries');
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;Schema routing during blue/green deployment
SQL — Schema Routing
-- Route specific user to new schema version
INSERT INTO mysql_query_rules
(rule_id, active, username, match_pattern, replace_pattern, apply, comment)
VALUES
(304, 1, 'beta_user', 'myapp\.', 'myapp_v2.', 0, 'Beta users → new schema');
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; Verify Rewriting Works
SQL — Verify Rewriting
-- Check rule hits to confirm rewriting is happening
SELECT rule_id, hits, match_pattern, replace_pattern, comment
FROM stats.stats_mysql_query_rules
WHERE replace_pattern IS NOT NULL
ORDER BY hits DESC;
-- Check query digest to see rewritten queries
SELECT digest_text, count_star, hostgroup
FROM stats.stats_mysql_query_digest
ORDER BY count_star DESC LIMIT 10; Rewriting Rules and Gotchas
- apply=0 means continue checking rules after rewriting — useful when you want to rewrite AND then route
- apply=1 stops processing — query is rewritten and routed to destination_hostgroup
- Use re_modifiers='CASELESS' for case-insensitive matching
- Test regex patterns carefully — a bad pattern can match unexpected queries
- ProxySQL uses POSIX ERE (Extended Regular Expressions)
SQL — Case-insensitive Rewriting
-- Case-insensitive rewriting
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, replace_pattern, re_modifiers, apply, comment)
VALUES
(310, 1, 'select', 'SELECT', 'CASELESS', 0, 'Uppercase all selects');
-- Test regex before deploying using mysql_query_rules_fast_routing
-- Check what ProxySQL would match:
SELECT * FROM mysql_query_rules WHERE active=1 ORDER BY rule_id;⚠ Warning: Always test query rewriting in a development environment first. A bad replace_pattern can corrupt queries silently.
