On Monday (April 27-th) Oracle released MySQL 8.0.20 Server and related products. You could check the release notes and official blog post. The focus of this release is binary log transaction compression to save disk space and network bandwidth, so there are no much new features for developers. Still here are the tings I noticed in the fore mentioned resources.
- New index-level optimizer hints for existing index hints (mind the difference!) that include the following.
- GROUP_INDEX/NO_GROUP_INDEX as equivalent to FORCE INDEX FOR GROUP BY/IGNORE INDEX FOR GROUP BY index hints;
- INDEX/NO_INDEX as equivalent to FORCE INDEX/IGNORE INDEX index hints;
- JOIN_INDEX/NO_JOIN_INDEX as equivalent to FORCE INDEX FOR JOIN/IGNORE INDEX FOR JOIN index hints;
- ORDER_INDEX/NO_ORDER_INDEX as equivalent to FORCE INDEX FOR ORDER BY/IGNORE INDEX FOR ORDER BY index hints;
- Hash join optimizations now supports also outer, anti and semi join types, which means that for the hash join to work it is no longer necessary to have at least one equi-join condition.
- There are several improvements to EXPLAIN statement:
- with FORMAT=TREE includes new inversion information for windowing functions. Unfortunately, I found no further information on this;
- with FORMAT=TREE provides more information about evaluated window functions matching that supplied for regular aggregates. You should now see lines like "Window aggregate with buffering";
- for ANALYZE there is now the possibility to stop the execution with KILL QUERY or CTRL+C key combination on the command line;
- ANALYZE should now support the FORMAT option with the only possible value of TREE as reflected in the release notes and the manual, but for me it results in error.
mysql> EXPLAIN ANALYZE FORMAT=TREE SELECT 1+1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FORMAT=TREE SELECT 1+1' at line 1
Anyway, I hope this is the first step in implementing support for this command in Workbench in future (see bug 97282).
There are of course several deprecations that include:
- Some improvements and deprecations to the SQL syntax related to INTO and UNION clauses (see SQL Syntax Notes from the release notes);
- VALUES function for INSERT … ON DUPLICATE KEY UPDATE statement is now deprecated as I guessed in my post for MySQL 8.0.19 release;
- ON ERROR before ON EMPTY clauses for column definitions in JSON_TABLE function;
- The mentioned above index hints.
Nothing changed in MySQL 8.0.20 Workbench, but there is still release and release notes.
In waiting for the next release you could update your code to avoid surprises from removed previously deprecated features.