Optimized permanent minimization in MySQL 8.0

Optimized permanent minimization in MySQL 8.0

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:


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:


Download the test table in instance 8.0.15:














Download the test table to copy 8.0.16:














Let’s see what we downloaded:




















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

num






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.














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:


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:















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:


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















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.