MySQL 8 was released in 2018 and is the next release after 5.7.
Since 2018 and as of today there was 27 minor versions bringing the last version to 8.0.27. It’s important because MySQL did bring a lot of feature enhancements in these minor versions as we’ll see in the next part.
A Common Table Expression (also known as WITH query) is a named temporary result set.
It exists only in the scope of a single statement and can be later referred within that statement.
You create a CTE using a WITH query, then reference it within a SELECT, INSERT, UPDATE, or DELETE statement.
CTEs make a query more readable, allow to better organize long queries and better reflects human logic (like functions does). It’s particularly useful when you need to reference a derived table multiple times in a single query. There is also a specific category of CTEs called recursive CTEs that are allowed to reference themselves. These CTEs can solve problems that cannot be addressed with other queries.
This feature is available as of MySQL 8.0 and some edge cases have been handled in 8.0.19 (recursive SELECT with LIMIT clause).
Documentation is here.
PostgreSQL’s documentation does an excellent job of introducing the concept of Window Functions:
A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. But unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result.
Here is an example that shows how to compare each employee’s salary with the average salary in his or her department:
MySQL comes with the following WINDOW functions:
WINDOW functions probably deserves an article on their own. You can find one here.
This feature is available as of MySQL 8.0.
Documentation is here.
MySQL now supports use of “expressions” as default values. Expressions are distinguished by the use of parenthesis. BLOB, TEXT, GEOMETRY, and JSON data types can be assigned a default value only if the value is written as an expression, even if the expression value is a literal.
This feature is available starting MySQL 8.0.13. Before 8.0.13, the only expression supported was CURRENT_TIMESTAMP.
Documentation is here.
MySQL now supports indexing expression values referencing other keys rather than column values or column prefixes. Using parts of a function key allows you to index values that are not directly stored in the table.
This feature is available starting MySQL 8.0.13. Prior to 8.0.13, you could achieve the same result by using virtual columns and indexing them but this is clearly way more straightforward.
Documentation is here.
MySQL now supports descending indexes (DESC). Previously, indexes could be scanned in reverse order but at a performance penalty. A descending index can be scanned in forward order, which is more efficient.
Descending indexes also make it possible for the optimizer to use multiple-column indexes when the most efficient scan order mixes ascending order for some columns and descending order for others.
This feature is available as of MySQL 8.0.
Documentation is here.
MySQL now supports invisible indexes. An invisible index is not used by the optimizer, but is otherwise maintained normally.
It’s basically a toggle for indexes.
Hidden index can be used to quickly test the impact of index deletion or index creation on query performance without index deletion and reconstruction. If the index is needed, it is good to set it visible again. This is undoubtedly very useful in large table testing, because it consumes performance for index deletion and addition of large table, and even affects the normal operation of the table.
This feature is available as of MySQL 8.0.
Documentation is here.
This statement provides expanded information about the execution of SELECT statements in TREE format. This includes startup cost, total cost, number of rows returned by iterator, and the number of loops executed.
The cost is an arbitrary unit but it is consistent between queries and usually a good proxy to answer the question “is this query faster than this other query?”.
This feature is available in 8.0.18.
Documentation is here.
MySQL 8 comes with tons of other features like:
->> operator, JSON_PRETTY(), merge function, aggregation functions, JSON schema validation draft 4 …)And of course performance and stability improvements.
The full list of changes is here.