{"id":1578,"date":"2016-02-25T10:45:45","date_gmt":"2016-02-25T09:45:45","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=1578"},"modified":"2016-03-18T14:13:47","modified_gmt":"2016-03-18T13:13:47","slug":"30-mins-with-mysql-query-rewriter","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2016\/02\/25\/30-mins-with-mysql-query-rewriter\/","title":{"rendered":"30 mins with MySQL Query Rewriter"},"content":{"rendered":"<p><em><a href=\"http:\/\/dasini.net\/blog\/2016\/03\/02\/30-mins-avec-mysql-query-rewriter\/\">Lire cet article en fran\u00e7ais<\/a><\/em><\/p>\n<h1>TL;TR<\/h1>\n<p>Sometime DBAs have to deal with problematic queries and cannot tackle the problem at the source (problematic queries from ORMs, third party apps,&#8230; or source unknown&#8230;).<\/p>\n<p>MySQL 5.7 provides :<\/p>\n<ul>\n<li>A pre and post parse query rewrite APIs\n<ul>\n<li>Users can write their own plug-ins<\/li>\n<li>You can eliminates many legacy use cases for proxies<\/li>\n<\/ul>\n<\/li>\n<li>With the post-parse query plugin, you can:\n<ul>\n<li>Rewrite problematic queries without the need to make application changes<\/li>\n<li>Add hints<\/li>\n<li>Modify join order<\/li>\n<li>&#8230;<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<h1>Query Rewrite Plugin APIs<\/h1>\n<p>Paraphrasing this <a href=\"http:\/\/mysqlserverteam.com\/the-query-rewrite-plugins\/\" target=\"_blank\">MySQL Server Blog&rsquo;s post<\/a> :<\/p>\n<p>\u00a0\u00bb<\/p>\n<p>MySQL now offer two APIs for writing query rewrite plugins.<\/p>\n<p>&#8211; <strong>Pre-parse rewrite plugin API<\/strong>,\u00a0 is for when you know exactly \u2013 as in character-by-character exactly \u2013 what the offending queries look like. This one has a hook to intercept the query string right before it\u2019s parsed.<\/p>\n<p>&#8211; <strong>Post-parse rewrite plugin API,<\/strong> comes in right after parsing and acts on the parse tree. It offers the basic functionality to walk over the parsed query, which is a lot more efficient than dealing with a string.<\/p>\n<p>\u00a0\u00bb<\/p>\n<p>This blog post is about the <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/rewriter-query-rewrite-plugin.html\" target=\"_blank\"><strong>Rewriter plugin<\/strong><\/a>, a post-parse query rewrite plugin, include in the <a href=\"http:\/\/www.thecompletelistoffeatures.com\/\" target=\"_blank\">MySQL 5.7<\/a> distribution (as of MySQL 5.7.6).<\/p>\n<p>&nbsp;<\/p>\n<h1>Rewriter plugin<\/h1>\n<h2>Installation and checks<\/h2>\n<p>You are in MySQL world, so that&rsquo;s obviously very easy! \ud83d\ude42<\/p>\n<p>To install the Rewriter query rewrite plugin, run\u00a0<strong>install_rewriter.sql<\/strong> located in the share directory of your MySQL installation.<\/p>\n<pre class=\"\">~ $ mysql -u root -p &lt; install_rewriter.sql<\/pre>\n<p>&nbsp;<\/p>\n<p>You can now check if everything is ok<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; SELECT * FROM mysql.plugin;\r\n+--------------------------+-------------+\r\n| name                     | dl          |\r\n+--------------------------+-------------+\r\n| rewriter                 | rewriter.so |\r\n+--------------------------+-------------+\r\n\r\nmysql&gt; SHOW GLOBAL VARIABLES LIKE 'rewriter%';\r\n+------------------+-------+\r\n| Variable_name    | Value |\r\n+------------------+-------+\r\n| rewriter_enabled | ON    |\r\n| rewriter_verbose | 1     |\r\n+------------------+-------+\r\n\r\nmysql&gt; SHOW GLOBAL STATUS LIKE 'rewriter%';\r\n+-----------------------------------+-------+\r\n| Variable_name                     | Value |\r\n+-----------------------------------+-------+\r\n| Rewriter_number_loaded_rules      | 0     |\r\n| Rewriter_number_reloads           | 1     |\r\n| Rewriter_number_rewritten_queries | 0     |\r\n| Rewriter_reload_error             | OFF   |\r\n+-----------------------------------+-------+<\/pre>\n<p>New database objects were also created<\/p>\n<pre class=\"lang:mysql decode:true\"># Stored procedure\r\n\r\nmysql&gt; SELECT ROUTINE_NAME, ROUTINE_TYPE FROM information_schema.ROUTINES \r\nWHERE ROUTINE_SCHEMA='query_rewrite';\r\n+---------------------+--------------+\r\n| ROUTINE_NAME        | ROUTINE_TYPE |\r\n+---------------------+--------------+\r\n| flush_rewrite_rules | PROCEDURE    |\r\n+---------------------+--------------+\r\n\r\n\r\n# UDF\r\n\r\nmysql&gt; SELECT name, dl, type FROM mysql.func \r\nWHERE name LIKE '%rewrite%';\r\n+--------------------+-------------+----------+\r\n| name               | dl          | type     |\r\n+--------------------+-------------+----------+\r\n| load_rewrite_rules | rewriter.so | function |\r\n+--------------------+-------------+----------+\r\n\r\n\r\n\r\n# New table is created in a new schema\r\n\r\nmysql&gt; SHOW TABLES IN query_rewrite;\r\n+-------------------------+\r\n| Tables_in_query_rewrite |\r\n+-------------------------+\r\n| rewrite_rules           |\r\n+-------------------------+<\/pre>\n<p>&nbsp;<\/p>\n<p>The <strong>rewrite_rules<\/strong> table in the <strong>query_rewrite <\/strong>database provides <strong>persistent storage<\/strong> for the rules that the Rewriter plugin uses to decide whether to rewrite statements.<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; SHOW CREATE TABLE query_rewrite.rewrite_rules\\G\r\n*************************** 1. row ***************************\r\n       Table: rewrite_rules\r\nCreate Table: CREATE TABLE `rewrite_rules` (\r\n  `id` int(11) NOT NULL AUTO_INCREMENT,\r\n  `pattern` varchar(10000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,\r\n  `pattern_database` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,\r\n  `replacement` varchar(10000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,\r\n  `enabled` enum('YES','NO') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'YES',\r\n  `message` varchar(1000) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,\r\n  `pattern_digest` varchar(32) DEFAULT NULL,\r\n  `normalized_pattern` varchar(100) DEFAULT NULL,\r\n  PRIMARY KEY (`id`)\r\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;<\/pre>\n<p>&nbsp;<\/p>\n<p>You can enable and disable the plugin at runtime :<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; SET GLOBAL rewriter_enabled = ON;\r\n\r\nmysql&gt; SET GLOBAL rewriter_enabled = OFF;<\/pre>\n<p>&nbsp;<\/p>\n<p>Obviously it also possible to enable the plugin in the configuration file (my.cnf | my.ini)<\/p>\n<pre class=\"lang:vim decode:true\">[mysqld]\r\n\r\nrewriter_enabled = ON<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<h1>Rewrite a query<\/h1>\n<p>Post parse rewrite plugin works <strong>only with SELECT statements<\/strong>.<\/p>\n<p>You can try non-select statements but you&rsquo;ll see, in rewrite_rules.message column, the following error message when you&rsquo;ll flush the rules:<\/p>\n<pre class=\"lang:vim decode:true\">          message: Pattern needs to be a select statement.<\/pre>\n<p>&nbsp;<\/p>\n<p>Rewriter plugin usage is very simple. Let&rsquo;s start with a dummy example: transform a SELECT n into a SELECT n+1 (n is an integer).<\/p>\n<p><span style=\"text-decoration: underline;\">Patterns<\/span><\/p>\n<p><strong><em>\u00a0-&gt;<\/em><\/strong> SELECT 1\u00a0\u00a0\u00a0\u00a0\u00a0 # Input<\/p>\n<p><strong><em>\u00a0&lt;=<\/em><\/strong> SELECT 2\u00a0\u00a0\u00a0 # Output<\/p>\n<h2>Add the rewrite rule<\/h2>\n<p>To add rules for the Rewriter plugin, add rows to the <strong>rewrite_rules <\/strong>table.<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; INSERT INTO query_rewrite.rewrite_rules (pattern, replacement) VALUES('SELECT ?', 'SELECT ? + 1');<\/pre>\n<p>&nbsp;<\/p>\n<p>rewrite_rules table content is:<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; SELECT * FROM query_rewrite.rewrite_rules\\G\r\n*************************** 1. row ***************************\r\n               id: 1\r\n          pattern: SELECT ?\r\n pattern_database: NULL\r\n      replacement: SELECT ? + 1\r\n          enabled: YES\r\n          message: NULL\r\n    pattern_digest: NULL\r\nnormalized_pattern: NULL<\/pre>\n<p>&nbsp;<\/p>\n<p>Rewriter&rsquo;s stats shows:<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; SHOW GLOBAL status LIKE '%rewriter%';\r\n+-----------------------------------+-------+\r\n| Variable_name                     | Value |\r\n+-----------------------------------+-------+\r\n| Rewriter_number_loaded_rules      | 0     |\r\n| Rewriter_number_reloads           | 1     |\r\n| Rewriter_number_rewritten_queries | 0     |\r\n| Rewriter_reload_error             | OFF   |\r\n+-----------------------------------+-------+<\/pre>\n<p>&nbsp;<\/p>\n<h2>Flush the rewrite rule<\/h2>\n<p>Then invoke the <strong>flush_rewrite_rules()<\/strong> stored procedure to load the rules from the table into the plugin.<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; CALL query_rewrite.flush_rewrite_rules();\r\nQuery OK, 0 rows affected (0,01 sec)\r\n<\/pre>\n<p>When the plugin reads each rule from the rules table, it computes a normalized form (digest) from the pattern and a digest hash value, and updates the normalized_pattern and pattern_digest columns:<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; SELECT * FROM query_rewrite.rewrite_rules\\G\r\n*************************** 1. row ***************************\r\n               id: 1\r\n          pattern: SELECT ?\r\n pattern_database: NULL\r\n      replacement: SELECT ? + 1\r\n          enabled: YES\r\n          message: NULL\r\n    pattern_digest: 2c7e64d74a4f06d8ceff62d23ae9180c\r\nnormalized_pattern: select ?<\/pre>\n<p>&nbsp;<\/p>\n<p>Within a pattern template, ? characters act as parameter markers that match data values. Parameter markers can be used only where data values should appear, <strong>not for SQL keywords, identifiers<\/strong>, and so forth. The ? characters should not be enclosed within quotation marks.<\/p>\n<p>&nbsp;<\/p>\n<p>If the query parsing failed the stored procedure will raise an error:<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; CALL query_rewrite.flush_rewrite_rules();\r\nERROR 1644 (45000): Loading of some rule(s) failed.<\/pre>\n<p>You have more details in query_rewrite.rewrite_rules.message column<\/p>\n<p>e.g.<\/p>\n<pre class=\"lang:mysql decode:true\">message: Parse error in replacement: \"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1\"<\/pre>\n<p>Rewriter&rsquo;s stats shows now:<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; SHOW GLOBAL status LIKE '%rewriter%';\r\n+-----------------------------------+-------+\r\n| Variable_name                     | Value |\r\n+-----------------------------------+-------+\r\n| Rewriter_number_loaded_rules      | 1     |\r\n| Rewriter_number_reloads           | 2     |\r\n| Rewriter_number_rewritten_queries | 0     |\r\n| Rewriter_reload_error             | OFF   |\r\n+-----------------------------------+-------+<\/pre>\n<p>&nbsp;<\/p>\n<h1>Rewritten query<\/h1>\n<p>So we added and flushed the rules, we can now execute a query according to the pattern and see the behaviour<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; SELECT 1;\r\n+-------+\r\n| 1 + 1 |\r\n+-------+\r\n|     2 |\r\n+-------+\r\n1 row in set, 1 warning (0,00 sec)<\/pre>\n<p>&nbsp;<\/p>\n<p>Magic !!!<\/p>\n<p>The query was rewritten \u00ab\u00a0on the fly\u00a0\u00bb. However some interesting details are hiding in the warning.<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; SHOW WARNINGS;\r\n+-------+------+------------------------------------------------------------------------+\r\n| Level | Code | Message                                                                |\r\n+-------+------+------------------------------------------------------------------------+\r\n| Note  | 1105 | Query 'select 1' rewritten to 'SELECT 1 + 1' by a query rewrite plugin |\r\n+-------+------+------------------------------------------------------------------------+<\/pre>\n<p>All we need to know are in the Note : 1105<\/p>\n<p>Rewriter&rsquo;s stats are updated accordingly:<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; SHOW GLOBAL status LIKE '%rewriter%';\r\n+-----------------------------------+-------+\r\n| Variable_name                     | Value |\r\n+-----------------------------------+-------+\r\n| Rewriter_number_loaded_rules      | 1     |\r\n| Rewriter_number_reloads           | 2     |\r\n| Rewriter_number_rewritten_queries | 1     |\r\n| Rewriter_reload_error             | OFF   |\r\n+-----------------------------------+-------+<\/pre>\n<p>&nbsp;<\/p>\n<p>To disable an existing rule, modify its <strong>enabled<\/strong> column and <strong>reload<\/strong> the table into the plugin:<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; UPDATE query_rewrite.rewrite_rules SET enabled = 'NO' WHERE id = 1;\r\n\r\nmysql&gt; CALL query_rewrite.flush_rewrite_rules();<\/pre>\n<p>&nbsp;<\/p>\n<p>You can also delete table rows<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; TRUNCATE TABLE query_rewrite.rewrite_rules;  # Delete all the rows (all the rules)\r\n\r\nmysql&gt; CALL query_rewrite.flush_rewrite_rules();<\/pre>\n<p>&nbsp;<\/p>\n<p>OK folks! Let&rsquo;s see more relevant examples<\/p>\n<p>&nbsp;<\/p>\n<h1>Rewrite plugin examples<\/h1>\n<h2>Ex 1<\/h2>\n<p>Rewrite a Join into a Sub-query; ie for performance reason you must rewrite the query but you don&rsquo;t have access to the app.<\/p>\n<p><span style=\"text-decoration: underline;\">Patterns<\/span><\/p>\n<p><em><strong>\u00a0-&gt;<\/strong><\/em> SELECT count(distinct emp_no) FROM employees.employees<strong> INNER JOIN<\/strong> employees.salaries USING(emp_no) WHERE DATEDIFF(to_date, from_date) &lt; <em>{integer}<\/em>;<\/p>\n<p><em>\u00a0&lt;=<\/em> SELECT count(emp_no) FROM employees.employees WHERE emp_no <strong>IN<\/strong> ( SELECT emp_no FROM employees.salaries WHERE DATEDIFF(to_date, from_date) &lt; <em>{integer}<\/em>);<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\">Without the rule<\/span><\/p>\n<pre class=\"lang:mysql decode:true\">SELECT count(distinct emp_no) \r\nFROM employees.employees INNER JOIN employees.salaries USING(emp_no) \r\nWHERE DATEDIFF(to_date, from_date) &lt; 2000;\r\n+------------------------+\r\n| count(distinct emp_no) |\r\n+------------------------+\r\n|                 300024 |\r\n+------------------------+\r\n\r\n1 row in set (12,93 sec)<\/pre>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\">Add the rewrite rule<\/span><\/p>\n<pre class=\"lang:mysql decode:true\">INSERT INTO query_rewrite.rewrite_rules \r\n(\r\npattern, \r\nreplacement\r\n) \r\nVALUES\r\n(\r\n'SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE DATEDIFF(to_date, from_date) &lt; ?', \r\n'SELECT count(emp_no) FROM employees.employees WHERE emp_no IN ( SELECT emp_no FROM employees.salaries WHERE DATEDIFF(to_date, from_date) &lt; ?)'\r\n);\r\n\r\n\r\nCALL query_rewrite.flush_rewrite_rules();<\/pre>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\">With the rule<\/span><\/p>\n<pre class=\"lang:mysql decode:true\">SELECT count(distinct emp_no) \r\nFROM employees.employees INNER JOIN employees.salaries USING(emp_no) \r\nWHERE DATEDIFF(to_date, from_date) &lt; 2000;\r\n+---------------+\r\n| count(emp_no) |\r\n+---------------+\r\n|        300024 |\r\n+---------------+\r\n\r\n1 row in set, 1 warning (3,77 sec)<\/pre>\n<p>The query time is dropped from 12.93 s to 3.77<\/p>\n<pre class=\"lang:mysql decode:true\">SHOW WARNINGS;\r\n*************************** 1. row ***************************\r\n Level: Note\r\n  Code: 1105\r\nMessage: Query 'SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE DATEDIFF(to_date, from_date) &lt; 2000' rewritten to 'SELECT count(emp_no) FROM employees.employees WHERE emp_no IN ( SELECT emp_no FROM employees.salaries WHERE DATEDIFF(to_date, from_date) &lt; 2000)' by a query rewrite plugin<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<h2>Ex 2<\/h2>\n<p>Limit query execution time; ie add MySQL 5.7 hint \/*+ <a href=\"http:\/\/mysqlserverteam.com\/server-side-select-statement-timeouts\/\" target=\"_blank\">MAX_EXECUTION_TIME<\/a>(X)*\/ that is query duration can&rsquo;t be more than X milliseconds<\/p>\n<p><span style=\"text-decoration: underline;\">Patterns<\/span><\/p>\n<p><strong><em>\u00a0-&gt;<\/em><\/strong> SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = <em>{integer}<\/em>;<\/p>\n<p><strong><em>\u00a0&lt;=<\/em><\/strong> SELECT<strong> \/*+ MAX_EXECUTION_TIME(10000)*\/<\/strong> count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = <em>{integer}<\/em>;<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\">Without the rule<\/span><\/p>\n<pre class=\"lang:mysql decode:true\">SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = 110000;\r\n1 row in set (11,82 sec)\r\n\r\nSELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = 70000;\r\n1 row in set (9,22 sec)<\/pre>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\">Add the rewrite rule<\/span><\/p>\n<pre class=\"lang:mysql decode:true \">INSERT INTO query_rewrite.rewrite_rules \r\n(\r\npattern, \r\nreplacement, \r\npattern_database\r\n) \r\nVALUES\r\n(\r\n'SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = ?', \r\n'SELECT \/*+ MAX_EXECUTION_TIME(10000)*\/ count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary =  ?', \r\n'employees'\r\n);\r\n\r\n\r\nCALL query_rewrite.flush_rewrite_rules();<\/pre>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\">With the rule<\/span><\/p>\n<pre class=\"lang:mysql decode:true\">SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = 110000;\r\nERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded\r\n\r\nSELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = 70000;\r\n1 row in set (9,85 sec)<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<h2>Ex 3<\/h2>\n<p>Schema evolution; ie table column added (or dropped) but you can&rsquo;t change the query (or not immediately)<\/p>\n<p><span style=\"text-decoration: underline;\">Patterns<\/span><\/p>\n<p><strong><em>\u00a0-&gt;<\/em><\/strong> SELECT first_name, last_name FROM employees.employees WHERE year(hire_date) = 2000;<\/p>\n<p><strong><em>\u00a0&lt;=<\/em><\/strong> SELECT first_name, last_name, <strong>birth_date<\/strong> FROM employees.employees WHERE year(hire_date) = 2000;<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\">Without the rule<\/span><\/p>\n<pre class=\"lang:mysql decode:true\">SELECT \r\n    first_name, \r\n    last_name \r\nFROM employees.employees WHERE year(hire_date) = 2000;\r\n+-------------+------------+\r\n| first_name  | last_name  |\r\n+-------------+------------+\r\n| Ulf         | Flexer     |\r\n\r\n...\r\n13 rows in set (0,01 sec)<\/pre>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\">Add the rewrite rule<\/span><\/p>\n<pre class=\"lang:mysql decode:true\">INSERT INTO query_rewrite.rewrite_rules \r\n(\r\npattern, \r\nreplacement, \r\npattern_database\r\n) \r\nVALUES\r\n(\r\n'SELECT first_name, last_name FROM employees.employees WHERE year(hire_date) = ?', \r\n'SELECT first_name, last_name, birth_date FROM employees.employees WHERE year(hire_date) =  ?', \r\n'employees'\r\n);\r\n\r\nCALL query_rewrite.flush_rewrite_rules();<\/pre>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\">With the rule<\/span><\/p>\n<pre class=\"lang:mysql decode:true\">SELECT \r\n    first_name, \r\n    last_name \r\nFROM employees.employees WHERE year(hire_date) = 2000;\r\n+-------------+------------+------------+\r\n| first_name  | last_name  | birth_date |\r\n+-------------+------------+------------+\r\n| Ulf         | Flexer     | 1960-09-09 |\r\n...\r\n\r\n13 rows in set (0,01 sec)<\/pre>\n<p>&nbsp;<\/p>\n<p>Other ideas ?<\/p>\n<p>&#8211; Index hints : the optimizer don&rsquo;t use the best index, so you can rewrite the query with USE | FORCE | IGNORE index<\/p>\n<p>&#8211; Prevent SELECT with \u00ab\u00a0infinite result, so you can rewrite the query adding LIMIT 1000 or whatever.<\/p>\n<p>Like mysql client text &#8211;safe-update option<\/p>\n<pre class=\"lang:sh decode:true\"> $ .\/mysql --help | grep dummy\r\n  -U, --i-am-a-dummy  Synonym for option --safe-updates, -U.<\/pre>\n<p>&#8230;<\/p>\n<p>Sky is the limit \ud83d\ude42<\/p>\n<p>&nbsp;<\/p>\n<p>The Rewriter plugin does not replace proper code and server optimisation neither fine tuned query.<\/p>\n<p>N<span id=\"result_box\" class=\"short_text\" lang=\"en\"><span class=\"hps alt-edited\">evertheless it can be very useful.<\/span><\/span><\/p>\n<p>&nbsp;<\/p>\n<p>Give it a try, it definitely worth more than 30 minutes.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<h1>Going further<\/h1>\n<p><span style=\"text-decoration: underline;\">Post-parse query rewrite plugin<\/span><\/p>\n<ul>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/rewriter-query-rewrite-plugin.html\" target=\"_blank\">The Rewriter Query Rewrite Plugin<\/a><\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/plugin-types.html#query-rewrite-plugin-type\" target=\"_blank\">Query Rewrite Plugins<\/a><\/li>\n<li><a href=\"http:\/\/mysqlserverteam.com\/the-query-rewrite-plugins\/\" target=\"_blank\">The Query Rewrite Plugins<\/a><\/li>\n<li><a href=\"http:\/\/www.slideshare.net\/MartinHanssonOracle\/the-query-rewrite-plugin-interface-writing-your-own-plugin\" target=\"_blank\">The Query Rewrite Plugin Interface: Writing Your Own Plugin<\/a><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\">Pre-parse query rewrite plugin<\/span><\/p>\n<ul>\n<li><a href=\"http:\/\/mysqlserverteam.com\/write-yourself-a-query-rewrite-plugin-part-1\/\" target=\"_blank\">Write Yourself a Query Rewrite Plugin: Part 1<\/a><\/li>\n<li><a href=\"http:\/\/mysqlserverteam.com\/write-yourself-a-query-rewrite-plugin-part-2\/\" target=\"_blank\">Write Yourself a Query Rewrite Plugin: Part 2<\/a><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>Thank you for using MySQL!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Sometime DBAs have to deal with problematic queries and cannot tackle the problem at the source (problematic queries from ORMs, third party apps,&#8230; or source unknown&#8230;).<br \/>\nMySQL 5.7 provides a pre and post parse query rewrite APIs where users can write their own plug-ins.<br \/>\nWith the post-parse query plugin, you can rewrite problematic queries without the need to make application changes, add hints, modify join order&#8230;<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"categories":[203],"tags":[268,266],"class_list":["post-1578","post","type-post","status-publish","format-standard","hentry","category-mysql-en","tag-mysql-5-7","tag-rewrite-plugin"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-ps","jetpack-related-posts":[{"id":4462,"url":"https:\/\/dasini.net\/blog\/2021\/04\/13\/heatwave-a-mysql-cloud-feature-to-speed-up-your-queries\/","url_meta":{"origin":1578,"position":0},"title":"HeatWave &#8211; A MySQL cloud feature to speed up your queries","author":"Olivier DASINI","date":"13 avril 2021","format":false,"excerpt":"If you have (too) long running select queries it is probably because of lack of relevant indexes, problematic schema that lead to poor queries or inadequate hardware. That said, sometime even if you doing it right, the query execution time could be too long regarding of what the application or\u2026","rel":"","context":"Dans &quot;Cloud&quot;","block_context":{"text":"Cloud","link":"https:\/\/dasini.net\/blog\/category\/cloud\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/04\/MySQL-Database-Service-HeatWave-Replication.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/04\/MySQL-Database-Service-HeatWave-Replication.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/04\/MySQL-Database-Service-HeatWave-Replication.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/04\/MySQL-Database-Service-HeatWave-Replication.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":2202,"url":"https:\/\/dasini.net\/blog\/2018\/03\/29\/mysql-security-the-connection-control-plugins\/","url_meta":{"origin":1578,"position":1},"title":"MySQL Security &#8211; The Connection-Control Plugins","author":"Olivier DASINI","date":"29 mars 2018","format":false,"excerpt":"An ordinary threat databases could face is an attempt to discover the password by systematically trying every possible combination (letters, numbers, symbols). This is known as a brute force attack. In this fourth episode of the\u00a0MySQL 5.7 Security series, we will see how the MySQL DBA can leverage the\u00a0Connection-Control Plugins\u2026","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql-en\/"},"img":{"alt_text":"MySQL Security","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/MySQL_DB_Lock2.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":2353,"url":"https:\/\/dasini.net\/blog\/2018\/04\/16\/mysql-security-mysql-enterprise-firewall\/","url_meta":{"origin":1578,"position":2},"title":"MySQL Security \u2013 MySQL Enterprise Firewall","author":"Olivier DASINI","date":"16 avril 2018","format":false,"excerpt":"In this seventh episode of the\u00a0MySQL Security series, we will see how MySQL Enterprise Firewall can help you to strengthen the protection of your data, in real-time, against cyber security threats like SQL Injection attacks by monitoring, alerting, and blocking unauthorized database activity without any changes to your applications.","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql-en\/"},"img":{"alt_text":"MySQL Security","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/MySQL_DB_Lock2.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":2136,"url":"https:\/\/dasini.net\/blog\/2018\/03\/01\/mysql-security-password-validation-plugin\/","url_meta":{"origin":1578,"position":3},"title":"MySQL Security \u2013 Password Validation Plugin","author":"Olivier DASINI","date":"1 mars 2018","format":false,"excerpt":"In this article, 1st of a MySQL 5.7 Security series, we will see how to enforce Strong Passwords with Password Validation Plugin when using MySQL 5.7. Authentication with ID and password is a very simple and common (because it\u2019s simple) way to secure the access to a resource, however the\u2026","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql-en\/"},"img":{"alt_text":"MySQL Security","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/MySQL_DB_Lock2.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":8393,"url":"https:\/\/dasini.net\/blog\/2025\/10\/07\/let-your-ai-dba-assistant-write-your-mysql-queries\/","url_meta":{"origin":1578,"position":4},"title":"Let Your AI DBA Assistant Write Your MySQL Queries","author":"Olivier DASINI","date":"7 octobre 2025","format":false,"excerpt":"Having explored the innovative MySQL HeatWave technology that converts Natural Language into SQL (Ask Your Database Anything: Natural Language to SQL in MySQL HeatWave), our next article in this series, dives into a practical use case demonstrating how an AI DBA Assistant can significantly simplify your query generation workflow. In\u2026","rel":"","context":"Dans &quot;AI&quot;","block_context":{"text":"AI","link":"https:\/\/dasini.net\/blog\/category\/ai\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2025\/10\/reltime_monitor.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2025\/10\/reltime_monitor.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2025\/10\/reltime_monitor.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2025\/10\/reltime_monitor.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":2222,"url":"https:\/\/dasini.net\/blog\/2018\/04\/04\/mysql-security-mysql-enterprise-audit\/","url_meta":{"origin":1578,"position":5},"title":"MySQL Security &#8211; MySQL Enterprise Audit","author":"Olivier DASINI","date":"4 avril 2018","format":false,"excerpt":"In order to spot database misuse and\/or to prove compliance to popular regulations including GDPR, PCI DSS, HIPAA, ... database administrators can be required to record and audit database activities. In this fifth episode of the\u00a0MySQL Security series, we will see what MySQL Enterprise Audit provide to help organizations implement\u2026","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql-en\/"},"img":{"alt_text":"MySQL Security","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/MySQL_DB_Lock2.png?resize=350%2C200","width":350,"height":200},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1578","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/comments?post=1578"}],"version-history":[{"count":15,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1578\/revisions"}],"predecessor-version":[{"id":3761,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1578\/revisions\/3761"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=1578"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=1578"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=1578"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}