How to use Query Rewriter Plugin to tune SQL in MySQL database II

The Query Rewriter Plugin in MySQL is a component that allows you to modify incoming SQL queries before execution. It provides the ability to transform, route, filter, or expand queries based on specific requirements.

If you have installed the plugin, the following SQL statements can be used to define your SQL replacement rules and error message handling.

INSERT INTO query_rewrite.rewrite_rules (message, pattern, replacement)
VALUES(Unique_ID, Original_SQL, Rewrite_SQL);

The query_rewrite.rewrite_rules table in MySQL stores the rules used by the Query Rewriter Plugin to rewrite SQL queries. The table has two columns:

Pattern - This column represents the pattern or condition that triggers the rewriting of a SQL query. It defines the specific query or query pattern to match.

Replacement - This column specifies the replacement or transformation that should be applied to the matched query or query pattern.

When a SQL query is executed, the Query Rewriter Plugin checks the query_rewrite.rewrite_rules table for matching patterns. If a pattern matches the executed query, the plugin rewrites the query using the corresponding replacement. This allows you to modify the query structure, optimize it, or add custom logic based on specific patterns or conditions.

Here is an example of SQL statement with a hardcoded literal, the SQL takes 2.1 seconds to execute with the following query plan.

SELECT *
FROM employee
WHERE emp_id in (SELECT emp_id id
FROM emp_subsidiary
where emp_dept < 'D')
ORDER BY emp_id LIMIT 1;


The following screen displays an alternative SQL statement with significantly improved performance identified by the Tosska DB Ace SQL tuning tool. This optimized SQL query is over 300 times faster than the original SQL due to the injection of JOIN ORDER hints.

Now, we need to deploy this alternative SQL to the database alongside the original SQL. However, a challenge arises with the condition "emp_dept < 'D'" which does not match the SQL text when using a SQL statement with a different hardcoded literal like "emp_dept < 'E'". Therefore, we must utilize Value Placeholders to substitute the hardcoded literals with a Placeholder "?" as shown below.

INSERT INTO query_rewrite.rewrite_rules (message, pattern, replacement)
VALUES(Unique_ID, Original_SQL, Rewrite_SQL);

Original_SQL
SELECT *
FROM employee
WHERE emp_id in (SELECT emp_id id
FROM emp_subsidiary
where emp_dept < ?)
ORDER BY emp_id LIMIT 1

Rewrite_SQL
select /*+ QB_NAME(QB1) JOIN_ORDER(`employee`@QB1, `emp_subsidiary`@QB2) */ *
from employee
where emp_id in (select /*+ QB_NAME(QB2) */ emp_id id
from emp_subsidiary
where emp_dept < ?)
order by emp_id limit 1

Tosska DB Ace for MySQL simplifies this process by automating the discovery of an optimized SQL alternative and the deployment of SQL with value placeholder replacements in a fully automated manner.

The Query Rewriter Plugin is powerful and easy to use. The most challenging aspect is finding a replacement SQL for your poorly performing SQL statement. Tosska DB Ace Enterprise for MySQL can assist you in automating this process, from identifying poorly performing SQL statements to rewriting SQL syntax and deploying replacement rules.

Tosska DB Ace Enterprise for MySQL - Tosska Technologies Limited

DBAM Tune Rewriter demo - YouTube