{"id":3031,"date":"2019-05-07T09:18:29","date_gmt":"2019-05-07T08:18:29","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=3031"},"modified":"2019-05-07T09:18:36","modified_gmt":"2019-05-07T08:18:36","slug":"constant-folding-optimization-in-mysql-8-0","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2019\/05\/07\/constant-folding-optimization-in-mysql-8-0\/","title":{"rendered":"Constant-Folding Optimization in MySQL 8.0"},"content":{"rendered":"\n

TL;TR<\/h2>\n\n\n\n

In MySQL 8.0.16<\/strong> the optimizer has improved again!
Comparisons of columns of numeric types with constant values are checked and folded or removed for invalid or out-of-rage values.
The goal is to speed up query execution<\/strong>. <\/p>\n\n\n\n

\n\n\n\n\n\n

The name of this article (Constant-Folding<\/a><\/strong> Optimization), named after this kind of optimization, is quite cryptic. Nevertheless the principle is simple and more important there is nothing to do from the user perspective.<\/p>\n\n\n\n

What is \u00ab\u00a0Constant-Folding Optimization\u00a0\u00bb ?<\/h2>\n\n\n\n

From the MySQL Documentation<\/a> :
Comparisons between constants and column values in which the constant value is out of range or of the wrong type with respect to the column type are now handled once during query optimization rather row-by-row than during execution<\/em>.<\/p>\n\n\n\n

From the MySQL Server Team Blog<\/a> :
The goal is to speed up execution at the cost of a little more analysis at optimize time.
Always true and false comparisons are detected and eliminated.
In other cases, the type of the constant is adjusted to match that of the field if they are not the same, avoiding type conversion at execution time<\/em>.<\/p>\n\n\n\n

Clear enough?<\/p>\n\n\n\n

One example is worth a thousand words, so let’s have a deeper look comparing the old behavior in MySQL 8.0.15 to the new one beginning with MySQL 8.0.16.<\/p>\n\n\n\n

I’m using the optimized MySQL Server Docker images<\/a>, created, maintained and supported by the MySQL team at Oracle.<\/p>\n\n\n\n

Deployment of MySQL 8.0.15 & MySQL 8.0.16:<\/p>\n\n\n\n

$ docker run --name=mysql_8.0.15 -e MYSQL_ROOT_PASSWORD=unsafe -d mysql\/mysql-server:8.0.15<\/code><\/pre>\n\n\n\n
$ docker run --name=mysql_8.0.16 -e MYSQL_ROOT_PASSWORD=unsafe -d mysql\/mysql-server:8.0.16<\/code><\/pre>\n\n\n\n
Note<\/u>:<\/p>
Obviously using a password on the command line interface can be insecure<\/strong>. <\/p>
Please read the best practices of deploying MySQL on Linux with Docker<\/a>.<\/p><\/blockquote>\n\n\n\n

Copy the test table dump file on 8.0.15 & 8.0.16:<\/p>\n\n\n\n
$ docker cp .\/testtbl.sql mysql_8.0.15:\/tmp\/testtbl.sql<\/code><\/pre>\n\n\n\n
$ docker cp .\/testtbl.sql mysql_8.0.16:\/tmp\/testtbl.sql<\/code><\/pre>\n\n\n\n

Load the test table into 8.0.15 instance:<\/p>\n\n\n\n
$ docker exec -it mysql_8.0.15 mysql -u root -p --prompt='mysql_8.0.15> '\n\nEnter password: \nWelcome to the MySQL monitor.  Commands end with ; or \\g.\nYour MySQL connection id is 31\nServer version: 8.0.15 MySQL Community Server - GPL\n\nCopyright (c) 2000, 2019, Oracle and\/or its affiliates. All rights reserved.\n\nOracle is a registered trademark of Oracle Corporation and\/or its\naffiliates. Other names may be trademarks of their respective\nowners.\n\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\n\nmysql_8.0.15> SELECT VERSION();\n+-----------+\n| VERSION() |\n+-----------+\n| 8.0.15    |\n+-----------+\n\nmysql_8.0.15> CREATE SCHEMA test;\nQuery OK, 1 row affected (0.04 sec)\n\nmysql_8.0.15> USE test\nDatabase changed\n\nmysql_8.0.15> source \/tmp\/testtbl.sql\n... <snip> ...<\/code><\/pre>\n\n\n\n

Load the test table into 8.0.16 instance:<\/p>\n\n\n\n
$ docker exec -it mysql_8.0.16 mysql -u root -p --prompt='mysql_8.0.16> '\n\nEnter password: \nWelcome to the MySQL monitor.  Commands end with ; or \\g.\nYour MySQL connection id is 12\nServer version: 8.0.16 MySQL Community Server - GPL\n\nCopyright (c) 2000, 2019, Oracle and\/or its affiliates. All rights reserved.\n\nOracle is a registered trademark of Oracle Corporation and\/or its\naffiliates. Other names may be trademarks of their respective\nowners.\n\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\n\nmysql_8.0.16> SELECT VERSION();\n+-----------+\n| VERSION() |\n+-----------+\n| 8.0.16    |\n+-----------+\n\nmysql_8.0.16> CREATE SCHEMA test;\nQuery OK, 1 row affected (0.04 sec)\n\nmysql_8.0.16> USE test\nDatabase changed\n\nmysql_8.0.16> source \/tmp\/testtbl.sql\n... <snip> ...<\/code><\/pre>\n\n\n\n


Let’s see what we have loaded:<\/p>\n\n\n\n
mysql_8.0.16> SHOW CREATE TABLE testtbl\\G\n*************************** 1. row ***************************\n       Table: testtbl\nCreate Table: CREATE TABLE `testtbl` (\n  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n  `val` varchar(36) NOT NULL,\n  `val2` varchar(36) DEFAULT NULL,\n  `val3` varchar(36) DEFAULT NULL,\n  `val4` varchar(36) DEFAULT NULL,\n  `num` int(10) unsigned DEFAULT NULL,\n  PRIMARY KEY (`id`),\n  KEY `idx2` (`val2`),\n  KEY `idx3` (`val3`),\n  KEY `idx4` (`val4`)\n) ENGINE=InnoDB AUTO_INCREMENT=14220001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci\n\n\nmysql_8.0.16> SELECT COUNT(*) FROM testtbl;\n+----------+\n| COUNT(*) |\n+----------+\n|  5000000 |\n+----------+<\/code><\/pre>\n\n\n\n
What is important for us here is the non indexed column – num<\/em> :

num<\/em><\/code> <\/em>int<\/em><\/strong>(10) <\/em>unsigned<\/em><\/strong> DEFAULT NULL
<\/em>
It contains only positive <\/strong>numbers:<\/p>\n\n\n\n
mysql_8.0.16> SELECT min(num), max(num) FROM testtbl;\n+----------+----------+\n| min(num) | max(num) |\n+----------+----------+\n|  9130001 | 14130000 |\n+----------+----------+<\/code><\/pre>\n\n\n\n\n\n
The old behavior<\/h2>\n\n\n\n
What happens if I looking for a negative number, let’s say -12345<\/strong>, on the  column num<\/em> ? 
Remember that it contains only positive numbers and there is no index. <\/p>\n\n\n\n
mysql_8.0.15> EXPLAIN SELECT * FROM testtbl WHERE num=-12345\\G\n*************************** 1. row ***************************\n           id: 1\n  select_type: SIMPLE\n        table: testtbl\n   partitions: NULL\n         type: ALL\npossible_keys: NULL\n          key: NULL\n      key_len: NULL\n          ref: NULL\n         rows: 4820634\n     filtered: 10.00\n        Extra: Using where<\/code><\/pre>\n\n\n\n
According to the EXPLAIN plan, we have a full table scan. In a way that  makes sense because there is no index on num<\/em>. 
However we know that there is no negative value<\/u>, so there is certainly some room for improvements \ud83d\ude42<\/p>\n\n\n\n
Running the query:<\/p>\n\n\n\n
mysql_8.0.15> SELECT * FROM testtbl WHERE num=-12345;\nEmpty set (2.77 sec)<\/code><\/pre>\n\n\n\n
Indeed the full table scan could be costly.<\/p>\n\n\n\n\n\n
The current behavior – 8.0.16+<\/h2>\n\n\n\n
The Constant-Folding Optimization<\/a> improves the execution of this type of queries.<\/p>\n\n\n\n
The EXPLAIN<\/a> plan for MySQL 8.0.16<\/strong> is completely different:<\/p>\n\n\n\n
mysql_8.0.16> EXPLAIN SELECT * FROM testtbl WHERE num=-12345\\G\n*************************** 1. row ***************************\n           id: 1\n  select_type: SIMPLE\n        table: NULL\n   partitions: NULL\n         type: NULL\npossible_keys: NULL\n          key: NULL\n      key_len: NULL\n          ref: NULL\n         rows: NULL\n     filtered: NULL\n        Extra: Impossible WHERE<\/code><\/pre>\n\n\n\n
Did you notice the:

    Extra: Impossible WHERE<\/strong><\/code><\/p>\n\n\n\n
Looking for the negative value in a strictly positive column was processed at the optimize time!
So they are obviously a positive impact<\/u> on the query execution time:<\/p>\n\n\n\n
mysql_8.0.16> SELECT * FROM testtbl WHERE num=-12345;\nEmpty set (0.00 sec)<\/code><\/pre>\n\n\n\n
Yay!<\/p>\n\n\n\n


In addition to the =<\/strong> operator, this optimization is currently possible for ><\/strong>, >=<\/strong>, <<\/strong>, <=<\/strong>, =<\/strong>, <>, !=<\/strong> and <=><\/strong> as well.
e.g.<\/p>\n\n\n\n
mysql_8.0.16> EXPLAIN SELECT * FROM testtbl WHERE num > -42 AND num <= -1 \\G\n*************************** 1. row ***************************\n           id: 1\n  select_type: SIMPLE\n        table: NULL\n   partitions: NULL\n         type: NULL\npossible_keys: NULL\n          key: NULL\n      key_len: NULL\n          ref: NULL\n         rows: NULL\n     filtered: NULL\n        Extra: Impossible WHERE\n\n\nmysql_8.0.16> SELECT * FROM testtbl WHERE num > -42 AND num <=  -1;\nEmpty set (0.00 sec)<\/code><\/pre>\n\n\n\n\n\n
Indexed column<\/h2>\n\n\n\n
As a side note, if your column is indexed the optimizer already have the relevant information, so before<\/u> 8.0.16, no need of Constant-Folding Optimization, to have a fast query :).

<\/p>\n\n\n\n
mysql_8.0.15> CREATE INDEX idx_num ON testtbl(num);\nQuery OK, 0 rows affected (24.84 sec)\nRecords: 0  Duplicates: 0  Warnings: 0\n\n\nmysql_8.0.15> EXPLAIN SELECT * FROM testtbl WHERE num = -12345\\G\n*************************** 1. row ***************************\n           id: 1\n  select_type: SIMPLE\n        table: NULL\n   partitions: NULL\n         type: NULL\npossible_keys: NULL\n          key: NULL\n      key_len: NULL\n          ref: NULL\n         rows: NULL\n     filtered: NULL\n        Extra: no matching row in const table\n1 row in set, 1 warning (0.00 sec)\n\n\nmysql_8.0.15> SELECT * FROM testtbl WHERE num = -12345;\nEmpty set (0.00 sec)<\/code><\/pre>\n\n\n\n
<\/p>\n\n\n\n\n\n
References<\/h2>\n\n\n\n