Optimized permanent minimization in MySQL 8.0.

Preamble

In MySQL 8.0.16 optimizer has improved again! Comparisons of columns of numeric types with constants are checked and stacked or deleted for invalid values or values that are out of control.

The goal is to speed up the query execution.

The title of this article (Constant Collapse Optimization), named after this type of optimization, is rather mysterious. However, the principle is simple and important, nothing can be done from the user’s point of view.

What is Optimization of Constant Collapse?

From MySQL documentation:

Comparisons between constants and column values in which the value of a constant is out of range or has the wrong type in relation to the column type are now processed once during the optimization of the query, row by row rather than during execution.

• The goal is to speed up execution by spending a little more time on analysis.
• Always true and false comparisons are detected and eliminated.
• In other cases, the type of constant is configured to match the field type if they do not match, thus avoiding type conversion at runtime.

One example costs a thousand words, so let’s take a deeper look comparing the old behavior in MySQL 8.0.15 with the new one starting from MySQL 8.0.16.

We use optimized MySQL Server Docker images created, maintained and supported by the MySQL team in Oracle.

Deploy MySQL 8.0.15 and MySQL 8.0.16:

$ docker run --name=mysql_8.0.15 -e MYSQL_ROOT_PASSWORD=unsafe -d mysql/mysql-server:8.0.15
$ docker run --name=mysql_8.0.16 -e MYSQL_ROOT_PASSWORD=unsafe -d mysql/mysql-server:8.0.16

Note: Obviously, using a password in the command line interface can be unsafe.

Please read the guidelines for deploying MySQL on Linux using Docker.

Copy the test table dump file to 8.0.15 and 8.0.16:

$ docker cp ./testtbl.sql mysql_8.0.15:/tmp/testtbl.sql
$ docker cp ./testtbl.sql mysql_8.0.16:/tmp/testtbl.sql

Download the test table in instance 8.0.15:

$ docker exec -it mysql_8.0.15 mysql -u root -p --prompt='mysql_8.0.15> '

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 8.0.15 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
It's not. Other may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql_8.0.15> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.15 |
+-----------+

mysql_8.0.15> CREATE SCHEMA test;
Query OK, 1 row affected (0.04 sec)

mysql_8.0.15> USE test
Database changed

mysql_8.0.15> source /tmp/testtbl.sql
... ...

Download the test table to copy 8.0.16:

$ docker exec -it mysql_8.0.16 mysql -u root -p --prompt='mysql_8.0.16> '.

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
It's not. Other may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql_8.0.16> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.16 |
+-----------+

mysql_8.0.16> CREATE SCHEMA test;
Query OK, 1 row affected (0.04 sec)

mysql_8.0.16> USE test
Database changed

mysql_8.0.16> source /tmp/testtbl.sql
... ...

Let’s see what we downloaded:

mysql_8.0.16> SHOW CREATE TABLE testtbl\G.
******************************* 1.
Table: testtbl
Create Table: CREATE TABLE `testtbl` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`val` varchar(36) NOT NULL,
`val2` varchar(36) DEFAULT NULL,
`val3` varchar(36) DEFAULT NULL,
`val4` varchar(36) DEFAULT NULL,
`num` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx2` (`val2`),
KEY `idx3` (`val3`),
KEY `idx4` (`val4`)
) ENGINE=InnoDB AUTO_INCREMENT=14220001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

mysql_8.0.16> SELECT COUNT(*) FROM testtbl;
+----------+
| COUNT(*) |
+----------+
| 5000000 |
+----------+

The unindexed column – num : int (10) unsigned DEFAULT NULL is important for us here. It contains only positive numbers:

num

mysql_8.0.16> SELECT min(num), max(num) FROM testtbl;
+----------+----------+
| min(num) | max(num) |
+----------+----------+
| 9130001 | 14130000 |
+----------+----------+

Old behavior

What happens if I find a negative number, say -12345, in the num column?
Remember that it contains only positive numbers and no index.

mysql_8.0.15> EXPLAIN SELECT * FROM testtbl WHERE num=-12345\G
******************************* 1.
id: 1
select_type: SIMPLE
table: testtbl
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4820634
filtered: 10.00
Extra: Using where

According to the EXPLAIN plan, we have a full table scan. So it makes sense, because there is no index on num.
However, we know that there is no negative value, so there is definitely room for improvement.

Executing the query:

mysql_8.0.15> SELECT * FROM testtbl WHERE num=-12345;
Empty set (2.77 sec)

Indeed, a full table scan can be expensive.

Current behavior; 8.0.16+

Constant-Foldable Optimization improves the execution of this type of requests.

The EXPLAIN plan for MySQL 8.0.16 is completely different:

mysql_8.0.16> EXPLAIN SELECT * FROM testtbl WHERE num=-12345\G.
******************************* 1.
id: 1
select_type: SIMPLE
table: NULL
partitions: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: Impossible WHERE
You noticed:

Extra: Impossible WHERE

Searching for a negative value in a strictly positive column was processed during the optimization!
Thus, they obviously have a positive effect on the time of request execution:

mysql_8.0.16> SELECT * FROM testtbl WHERE num=-12345;
Empty set (0.00 sec)

In addition to the operator = this optimization is now also possible for > , > = , < , <= , = , <>,! = and <=> .
For example:

mysql_8.0.16> EXPLAIN SELECT * FROM testtbl WHERE num > -42 AND num <= -1 \G.
******************************* 1.
id: 1
select_type: SIMPLE
table: NULL
partitions: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: Impossible WHERE

mysql_8.0.16 > > SELECT * FROM testtbl WHERE num > > - 42 AND num <= - 1;
Empty set ( 0.00 sec)

Indexed column

In addition, if your column is indexed by the optimizer, it already has the corresponding information, so up to 8.0.16 there is no need for constant optimization of minimization to have a quick smile request.

mysql_8.0.15> CREATE INDEX idx_num ON testtbl(num);
Query OK, 0rows affected (24.84 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql_8.0.15> EXPLAIN SELECT * FROM testtbl WHERE num = -12345\G
******************************* 1.
id: 1
select_type: SIMPLE
table: NULL
partitions: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: no matching row in consttable
1 row in set, 1 warning (0.00 sec)

mysql_8.0.15> SELECT * FROM testtbl WHERE num = -12345;
Empty set (0.00 sec)

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.