MySQL Hypergraph Optimizer

During the last MySQL & HeatWave Summit, Wim Coekaerts announced that a new optimizer is available and is already enabled in MySQL HeatWave.

Let’s have a quick look at it and how to use it.

The first step is to verify that Hypergraph is available:

MySQL > SET SESSION optimizer_switch='hypergraph_optimizer=on';
ERROR: 3999 (42000): The hypergraph optimizer does not yet support 
'use in non-debug builds'

The statement won’t return any error if the Hypergraph Optimizer is available.

Let’s try it and see the differences between the traditional optimizer and the new one. We will use the following query (rank employees by highest salary per department using a CTE and a Window Function):

MySQL > WITH salary_rank AS (     
     SELECT e.emp_no, e.first_name, e.last_name, d.dept_no, s.salary,
     RANK() OVER (
        PARTITION BY d.dept_no ORDER BY s.salary DESC
     ) AS dept_rank     
     FROM employees e     
     JOIN dept_emp d ON e.emp_no = d.emp_no     
     JOIN salaries s ON e.emp_no = s.emp_no     
    WHERE s.to_date = '9999-01-01' AND d.to_date = '9999-01-01' 
    ) SELECT * FROM salary_rank WHERE dept_rank = 1;

With the traditional optimizer, this is the output:

+--------+------------+-----------+---------+--------+-----------+
| emp_no | first_name | last_name | dept_no | salary | dept_rank |
+--------+------------+-----------+---------+--------+-----------+
| 466852 | Akemi      | Warwick   | d001    | 145128 |         1 |
| 413137 | Lunjin     | Swick     | d002    | 142395 |         1 |
| 421835 | Yinlin     | Flowers   | d003    | 141953 |         1 |
| 430504 | Youjian    | Cronau    | d004    | 138273 |         1 |
|  13386 | Khosrow    | Sgarro    | d005    | 144434 |         1 |
| 472905 | Shin       | Luck      | d006    | 132103 |         1 |
|  43624 | Tokuyasu   | Pesch     | d007    | 158220 |         1 |
| 425731 | Ramachenga | Soicher   | d008    | 130211 |         1 |
|  18006 | Vidya      | Hanabata  | d009    | 144866 |         1 |
+--------+------------+-----------+---------+--------+-----------+
9 rows in set (2.1155 sec)

Now with the Hypergraph, the output is the following:

+--------+------------+-----------+---------+--------+-----------+
| emp_no | first_name | last_name | dept_no | salary | dept_rank |
+--------+------------+-----------+---------+--------+-----------+
| 466852 | Akemi      | Warwick   | d001    | 145128 |         1 |
| 413137 | Lunjin     | Swick     | d002    | 142395 |         1 |
| 421835 | Yinlin     | Flowers   | d003    | 141953 |         1 |
| 430504 | Youjian    | Cronau    | d004    | 138273 |         1 |
|  13386 | Khosrow    | Sgarro    | d005    | 144434 |         1 |
| 472905 | Shin       | Luck      | d006    | 132103 |         1 |
|  43624 | Tokuyasu   | Pesch     | d007    | 158220 |         1 |
| 425731 | Ramachenga | Soicher   | d008    | 130211 |         1 |
|  18006 | Vidya      | Hanabata  | d009    | 144866 |         1 |
+--------+------------+-----------+---------+--------+-----------+
9 rows in set (1.6108 sec)

We can see that the query is faster. Other than that, nothing else seems different.

We can check the Query Execution Plan (output of EXPLAIN) and compare. Please note that the new Hypergraph Optimizer is only compatible with the formats TREE and JSON (v2):

ERROR: 3999 (42000): The hypergraph optimizer does not yet support 'EXPLAIN with TRADITIONAL format'

Query Execution Plan with the traditional optimizer:

Query Execution Plan with the new hypergraph optimizer:

We can see that both Query Execution Plans differ, of course. But please note that comparing costs between two optimizers doesn’t make sense, as the cost models are different.

In the example above, the cost of the fastest query that uses the hypergraph optimizer seems to be higher, but, once again, they are not comparable. We can only compare costs between plans using the same optimizer.

Can we switch to the new optimizer with complete confidence?

As with almost everything related to MySQL, the answer is it depends! 😉

Let’s take a look at the following query:

MySQL > SELECT d.dept_name, COUNT(e.emp_no) AS employee_count 
        FROM departments d 
        JOIN dept_emp de ON d.dept_no = de.dept_no 
        JOIN employees e ON de.emp_no = e.emp_no 
        GROUP BY d.dept_name;

With the traditional optimizer, the query returns the following output:

+--------------------+----------------+
| dept_name          | employee_count |
+--------------------+----------------+
| Customer Service   |          23580 |
| Development        |          85707 |
| Finance            |          17346 |
| Human Resources    |          17786 |
| Marketing          |          20211 |
| Production         |          73485 |
| Quality Management |          20117 |
| Research           |          21126 |
| Sales              |          52245 |
+--------------------+----------------+
9 rows in set (0.5184 sec)

Now, with the new hypergraph optimizer, this is the output:

+--------------------+----------------+
| dept_name          | employee_count |
+--------------------+----------------+
| Development        |          85707 |
| Sales              |          52245 |
| Production         |          73485 |
| Human Resources    |          17786 |
| Research           |          21126 |
| Quality Management |          20117 |
| Customer Service   |          23580 |
| Marketing          |          20211 |
| Finance            |          17346 |
+--------------------+----------------+
9 rows in set (0.3573 sec)

We can see that the query is faster, but the order of the rows returned is different.

But that kind of problem could also happen between previous major versions. If the record order is essential, then the query should sort them like this, for example:

MySQL > SELECT d.dept_name, COUNT(e.emp_no) AS employee_count 
        FROM departments d 
        JOIN dept_emp de ON d.dept_no = de.dept_no 
        JOIN employees e ON de.emp_no = e.emp_no 
        GROUP BY d.dept_name ORDER BY employee_count;

During my test, I also encountered slower queries with the new optimizer.

For example:

MySQL > WITH high_earners AS (
          SELECT emp_no, salary
          FROM salaries
          WHERE salary > 100000
        )
        SELECT e.first_name, e.last_name, h.salary
        FROM employees e
        JOIN high_earners h ON e.emp_no = h.emp_no;

With the traditional optimizer:

94696 rows in set (0.0086 sec)

With the hypergraph optimizer:

94696 rows in set (0.0767 sec)

For more details, I recommend watching Norvald’s session.

Conclusion

The new Hypergraph Optimizer, which has been enabled by default when planning for RAPID since 8.0.31, is not yet the default optimizer everywhere, and it’s still in development.

However, you can already test it; it usually provides a faster execution plan for more complex queries.

Nevertheless, this is a significant change that could affect the results of your queries, so please verify them before enabling it globally. This is also a recommended process for any major updates anyway, isn’t it 😉