MySQL - enabling the hypergraph optimizer

MySQL has provided the new hypergraph optimizer in the community edition of MySQL version 9.7.

To upgrade to it, you'll need to enable it in your Yum repo (for CentOS):

/etc/yum.repos.d/mysql-community.repo

[mysql-9.7-lts-community]
name=MySQL 9.7 LTS Community Server
baseurl=https://repo.mysql.com/yum/mysql-9.7-community/el/9/$basea
rch/
enabled=1
gpgcheck=0

And run yum update.

If you want the hypergraph optimizer enabled for everyone you can amend the /etc/my.cnf and add in:

optimizer_switch="hypergraph_optimizer=on"

There will likely be some queries that run slower, but for these you can use a hint to disable hypergraph, by adding:

/*+ SET_VAR(optimizer_switch='hypergraph_optimizer=off') */

It may also be wise to monitor the performance of your queries to ensure you do not have any negative performance impacts.
We use the pt query digest tool to analyse how the database as a whole is performing.