Query Rewrite

Overview

ProxySQL’s query rewriting feature allows administrators to dynamically modify queries without changing application code. Rules are defined in mysql_query_rules using the replace_pattern column.

Key Benefits

Improve Performance — Optimise query structures to reduce load times
Enforce Standards — Standardise queries automatically across the system
Enhance Security — Limit executable query types to reduce attack surface
Reduce Resource Usage — Transform SELECT * into selective column queries

Implementation

Connect to the ProxySQL admin interface:

mysql -u admin -padmin -h 127.0.0.1 -P 6032

Insert rewrite rules into mysql_query_rules:

INSERT INTO mysql_query_rules
  (rule_id, active, match_pattern, replace_pattern, apply)
VALUES
  (1, 1, 'SELECT \* FROM users', 'SELECT id, username, email FROM users', 1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Common Use Cases

Replace SELECT * with Specific Columns

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, replace_pattern, apply)
VALUES (1, 1, 'SELECT \* FROM users', 'SELECT id, username, email FROM users', 1);

Add USE INDEX

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, replace_pattern, apply)
VALUES (2, 1, 'SELECT (.+) FROM users WHERE username',
        'SELECT \1 FROM users USE INDEX (index_username) WHERE username', 1);

Add ORDER BY

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, replace_pattern, apply)
VALUES (3, 1, 'SELECT (.+) FROM products$',
        'SELECT \1 FROM products ORDER BY name ASC', 1);

Redact Sensitive Data

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, replace_pattern, apply)
VALUES (4, 1, 'SELECT ssn', 'SELECT "REDACTED" AS ssn', 1);

Capture Groups and Backreferences

match_pattern can capture parts of the original query with numbered parentheses. replace_pattern can then insert those captured values with \1, \2, and subsequent numbered backreferences. Capture numbers follow the opening-parenthesis order.

ProxySQL selects the regular-expression engine with mysql-query_processor_regex: 1 is PCRE (the default) and 2 is RE2. Both released engines support capture groups in match_pattern and numbered backreferences in replace_pattern. PCRE accepts a broader regular-expression syntax, while RE2 deliberately excludes constructs such as look-around and backreferences inside the match expression. Keep the match pattern within the selected engine’s syntax and test it before changing engines.

SQL Escaping

The Admin interface stores the replacement backreference as a single backslash followed by its number. Write '\1', not '$1' or '\\1', in the SQL statement. Regular-expression escapes in match_pattern likewise use one backslash. Escape a literal SQL single quote by doubling it, for example ''pending'' inside the pattern:

-- Captures a WHERE clause containing: status = 'pending'
'( WHERE.*status\s*=\s*''pending''.*)'

Modifiers

Set re_modifiers to CASELESS for case-insensitive matching. Add GLOBAL to replace every non-overlapping match rather than only the first. With the default PCRE engine, comma-separate combined modifiers as CASELESS,GLOBAL. The released RE2 path does not support CASELESS and GLOBAL together; choose one modifier or switch to PCRE after testing. GLOBAL controls replacement count, not how many capture groups are available.

Runnable Example

The following rule preserves a captured number while changing the result-column alias:

DELETE FROM mysql_query_rules WHERE rule_id = 7100;

INSERT INTO mysql_query_rules
  (rule_id, active, match_pattern, replace_pattern, re_modifiers, apply, comment)
VALUES
  (7100, 1,
   '^SELECT ([0-9]+) AS legacy_id$',
   'SELECT \1 AS current_id',
   'CASELESS', 1,
   'Capture-group example');

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Run the query through ProxySQL’s MySQL listener, not the Admin interface:

SELECT 42 AS legacy_id;

The backend receives SELECT 42 AS current_id. After exercising the rule, verify its counter on the Admin interface:

SELECT rule_id, hits
FROM stats_mysql_query_rules
WHERE rule_id = 7100;

stats_mysql_query_rules records rule_id and hits; loading query rules to runtime resets the counters. See stats_mysql_query_rules.

Rollback

Remove the example from every configuration layer:

DELETE FROM mysql_query_rules WHERE rule_id = 7100;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;