MySQL 8.0 is Out! Are We There Yet?

What is new?

  • Supports terabyte-sized databases even though it was created to handle 100M
  • Supports SQL standards as new as SQL 2016

New Defaults and Variable in MySQL 8.0

  • The default charset in MySQL 8.0 is utf8mb4
  • That means you can have mathematical equations and emojis
  • Binary log (log_bin) is enabled by default
  • SHA-2 for authentification using SSL
  • A mandatory default value for TIMESTAMPS etc

InnoDB performance

MySQL id doing certain new things in the database world.

Transaction Scheduling (CATS vs FIFO)

Usually, databases use FIFO (First In First Out) for scheduling transactions, now MySQL 8.0 uses CAT (Conventional Aware Transaction Scheduling)

Other important changes include - Invisible Indexes (Not used by the optimizer) - Descending Indexes

Windows Functions

Window functions, aka analytical functions, are functions which operate on a set of rows and return a single aggregated value. 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.

Example of Windows Functions

  • Enumerate rows, ROW_NUMBER()
  • Show aggregated sums, SUM()
  • Rank results, RANK()
  • Look at neighboring rows, LEAD() and LAG()

CTE - Common Table Expressions

Allows us to create like a temporary table, with a capability to refer other tables and other CTEs. CTE is like a table but doesn't need a CREATE privilege. CTEs can also be recursive and reference themselves.

CTEs simplify complex queries with complex joins and subqueries. A CTE can be used to:

  • Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.

  • Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.

  • Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.

  • Reference the resulting table multiple times in the same statement.

Subscribe for coding videos

Keep growing professionally with just three curated videos weekly.

We hate spam as much as you do.