MySQL 8.0 Highlights: Invisible Indexes, Persistent Settings, UTF8MB4, CTEs, and Window Functions

1. Invisible (Hidden) Indexes

MySQL 8.0 allows indexes to be marked as INVISIBLE, meaning the optimizer will ignore them during query planning. This is useful for performance debugging: hide an index, observe any slowdown, then decide whether to keep, reveal ( VISIBLE), or drop the index.

ALTER TABLE t ALTER INDEX i INVISIBLE;

ALTER TABLE t ALTER INDEX i VISIBLE;

When an index is invisible, the SHOW INDEX output shows Visible = NO. Note that invisible indexes are still maintained; long‑term hiding offers no benefit over simply dropping the index.

2. Persistent System Variables (SET PERSIST)

Runtime changes made with SET GLOBAL are temporary and lost after a restart. MySQL 8.0 introduces SET PERSIST, which writes the variable to mysqld-auto.cnf in the data directory, making the change survive restarts.

SET PERSIST max_connections = 500;

3. Default UTF‑8 Encoding

From MySQL 8.0 onward the default character set is utf8mb4, which fully supports all Unicode characters, including emojis, eliminating the need to manually switch from the older latin defaults.

4. Common Table Expressions (CTE)

Complex queries that previously required nested sub‑queries can now use the WITH clause for better readability and maintainability.

WITH
  t1 AS (SELECT col1 FROM table1),
  t2 AS (SELECT col2 FROM table2)
SELECT t1.*, t2.*
FROM t1, t2;

CTEs make query structure clearer and simplify modifications.

5. Window Functions

MySQL 8.0 adds window functions, enabling ranking and analytic calculations without collapsing rows via GROUP BY. Example using RANK() to order classes by student count:

SELECT *, RANK() OVER w AS `rank`
FROM classes
WINDOW w AS (ORDER BY stu_count);

Window functions can also compute aggregates across the entire result set, such as total student count or each class's proportion:

SELECT *, SUM(stu_count) OVER() AS total_count
FROM classes;

SELECT *, (stu_count) / (SUM(stu_count) OVER()) AS rate
FROM classes;

These features provide powerful analytical capabilities directly within MySQL.