New MySQL 8 Features: Invisible Indexes, Persistent Settings, UTF8MB4, CTEs, and Window Functions

You may already know that MySQL has offered NoSQL storage since version 5.7, and MySQL 8 improves that feature, but it is rarely used; this article therefore focuses on relational database improvements.

1. Invisible Indexes

Invisible indexes are useful for performance debugging. In MySQL 8 an index can be marked INVISIBLE so the optimizer will ignore it. By hiding an index you can observe its impact on performance; if performance degrades, the index is useful and can be made visible again, otherwise it can be dropped.

Hide an index:

ALTER TABLE t ALTER INDEX i INVISIBLE;

Make it visible again:

ALTER TABLE t ALTER INDEX i VISIBLE;

When an index is invisible, the SHOW INDEX output shows its Visible column as NO . Note that an invisible index is still kept up‑to‑date, so long‑term invisibility is not recommended.

2. Persistent Settings

Runtime changes via SET GLOBAL are temporary and lost after a restart. MySQL 8 adds SET PERSIST to write changes to mysqld-auto.cnf , which is read on the next startup and overrides the default configuration file.

Example:

SET PERSIST max_connections = 500;

3. UTF‑8 Encoding

Starting with MySQL 8 the default character set is utf8mb4 , which includes all emoji characters, eliminating the need to manually change the default from latin to avoid garbled text.

4. Common Table Expressions (CTE)

CTEs make complex queries easier to read and maintain. Instead of nested sub‑queries like:

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

you can write:

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

For a detailed description, refer to the official MySQL documentation.

5. Window Functions

MySQL 8 introduces window functions, which allow ranking and aggregation without collapsing rows. Example ranking class sizes:

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

Result shows each class with its rank. You can also compute totals without a GROUP BY :

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

And calculate each class's proportion of the total:

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

These window functions simplify queries that need per‑row calculations based on the entire result set.