Skip to main content

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);

See Also​