New features in MySQL 8

MySQL 8 introduced several powerful new features and improvements that make it a significant update over previous versions. Here are some of the key new features in MySQL 8:

1. Window Functions

• MySQL 8 added support for window functions like ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LEAD(), and LAG(). These functions allow for more advanced analytical queries, letting users calculate moving averages, ranks, cumulative sums, and more.
  

* Use RANK

 SELECT  s.student_id, s.student_name, SUM(p.score) AS total_score,
 RANK() OVER (ORDER BY SUM(p.score) DESC) AS rank_score
 FROM demo.student s
 JOIN demo.point p ON s.student_id = p.student_id
 GROUP BY s.student_id, s.student_name
 ORDER BY rank_score DESC

* Error

 SELECT  s.student_id, s.student_name, SUM(p.score) AS total_score,
 RANK() OVER (ORDER BY SUM(p.score) DESC) AS rank_score
 FROM demo.student s
 JOIN demo.point p ON s.student_id = p.student_id
 WHERE rank_score > 3
 GROUP BY s.student_id, s.student_name
 ORDER BY rank_score DESC

=> The issue here is that window functions like RANK() cannot be used directly in the WHERE clause because they are applied after the GROUP BY and SELECT operations. You need to compute the ranking in a subquery or CTE and then filter on it in the outer query.

* Fix use subquery

SELECT student_id, student_name, total_score, rank_score
FROM (
    SELECT s.student_id, s.student_name,
           SUM(p.score) AS total_score,
           RANK() OVER (ORDER BY SUM(p.score) DESC) AS rank_score
    FROM demo.student s
    JOIN demo.point p ON s.student_id = p.student_id
    GROUP BY s.student_id, s.student_name
) AS ranked_students
WHERE rank_score > 3
ORDER BY rank_score DESC;

* Fix use CTE

WITH RankedStudents AS (
    SELECT s.student_id, s.student_name,
           SUM(p.score) AS total_score,
           RANK() OVER (ORDER BY SUM(p.score) DESC) AS rank_score
    FROM demo.student s
    JOIN demo.point p ON s.student_id = p.student_id
    GROUP BY s.student_id, s.student_name
)
SELECT student_id, student_name, total_score, rank_score
FROM RankedStudents
WHERE rank_score > 3
ORDER BY rank_score DESC;

Key Differences:

Aspect	Subquery	CTE
Structure	Defined inline in the FROM clause.	Defined before the SELECT statement.
Readability	Can be harder to read for complex queries.	Easier to read and maintain, especially for complex queries.
Reuse	Cannot be reused within the same query.	Can be reused multiple times in the query (in case of multiple references to the same CTE).
Scope	Available only within the query block in which it is defined.	Available throughout the query until the end.
Performance	Often optimized by the database engine but may involve recalculating the subquery each time it is referenced.	Sometimes optimized by the database, and CTEs are often materialized (especially in PostgreSQL, but not always in MySQL).
Materialization	Subquery results might be materialized only once in certain scenarios, but not guaranteed.	In MySQL, CTEs are generally not materialized, so they are evaluated each time they are referenced.

Conclusion:

• Use Subqueries when:
  • You have a simpler query.
  • You don’t need to reference the same intermediate result multiple times.
  • Performance is slightly more important than readability.
• Use CTEs when:
  • You have a complex query that would benefit from being split into logical steps.
  • You need to reference the same intermediate result multiple times in the query.
  • You prioritize readability and maintainability over minor performance differences.

In Your Case:

Since both the subquery and the CTE versions are likely to perform similarly in MySQL 8.0, the decision comes down to readability. If you are working with a simple query, a subquery might suffice. If you expect the query to grow in complexity or if you want a cleaner structure, a CTE would be more maintainable.

2. Common Table Expressions (CTEs)

• With CTEs, users can define temporary result sets to be referenced within a SELECT, INSERT, UPDATE, or DELETE query. MySQL 8 supports recursive CTEs, making it easier to work with hierarchical data, such as organizational charts or file systems.

WITH RECURSIVE cte AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

3. JSON Enhancements

• MySQL 8 includes additional functions for handling JSON data, including JSON_TABLE() (which turns JSON data into a relational format) and JSON_OVERLAPS() (to check if two JSON arrays have common elements).

4. Improved Indexing with Invisible Indexes

• MySQL 8 introduced invisible indexes, which let you mark an index as invisible to the query optimizer. This is useful for testing how queries perform without a specific index, without fully deleting it.

ALTER TABLE table_name ALTER INDEX index_name INVISIBLE;

5. Roles for Better Access Control

• MySQL 8 added support for roles, allowing for easier management of user permissions. Roles can be created with specific privileges and then assigned to users, simplifying complex privilege assignments.

CREATE ROLE read_only;
GRANT SELECT ON database.* TO read_only;
GRANT read_only TO user_name;

6. Generated Columns

• MySQL 8 introduced generated (computed) columns, which can store data based on expressions or other column values. Generated columns can be virtual (calculated on-the-fly) or stored (physically stored in the table).

ALTER TABLE employees
ADD COLUMN full_name VARCHAR(255) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL;

7. Better Support for Geographic Data with Spatial Indexing

• MySQL 8 improved spatial data support by adding spatial indexes for InnoDB tables, making it more efficient for geographic or location-based queries.

8. Atomic DDL Statements

• In MySQL 8, DDL (Data Definition Language) operations are atomic and crash-safe, meaning that a failure during a CREATE, ALTER, DROP, or RENAME operation can be rolled back automatically.

9. Improved UTF-8 Support with utf8mb4

• MySQL 8 made utf8mb4 the default character set, supporting a wider range of Unicode characters, including emojis. The utf8mb4 encoding is now recommended for improved compatibility with modern web applications.

10. Descending Indexes

• MySQL 8 allows users to create descending indexes, optimizing queries that need sorted data in descending order without needing additional sorting at query time.

CREATE INDEX idx_desc ON table_name(column_name DESC);

11. Histograms for Better Query Optimization

• MySQL 8 supports histograms, which provide more accurate statistics for the query optimizer, helping it choose the best execution plans for complex queries. Histograms work especially well for non-indexed columns.

ANALYZE TABLE table_name UPDATE HISTOGRAM ON column_name;

12. Improved Performance Schema and Sys Schema

• MySQL 8 expanded the Performance Schema and introduced the Sys Schema as a helper schema, making it easier to monitor and optimize database performance. The Sys Schema provides user-friendly views for checking performance metrics.

13. Resource Groups

• Resource Groups in MySQL 8 allow users to allocate specific CPU resources to certain queries or workloads, improving control over query performance for different tasks.

CREATE RESOURCE GROUP rg1 TYPE = USER VCPU = 1,2;

14. Optimizer Hints with SET_VAR

• MySQL 8 introduced the SET_VAR hint, allowing users to override session-level system variables for specific queries, such as changing the max_execution_time for a single query.

SELECT /*+ SET_VAR(max_execution_time=5000) */ * FROM large_table;

15. Improved InnoDB and Backup Features

• Enhancements to InnoDB include improved support for bulk loading, reduced redo log size, and better crash recovery. Backup performance and reliability are also improved with these changes.

These features make MySQL 8 far more powerful, flexible, and performant for complex, high-demand applications.

==============

Example database

CREATE TABLE IF NOT EXISTS `point` (
  `point_id` int NOT NULL,
  `student_id` int DEFAULT NULL,
  `subject` varchar(50) DEFAULT NULL,
  `score` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`point_id`),
  KEY `student_id` (`student_id`),
  CONSTRAINT `point_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table demo.point: ~10 rows (approximately)
INSERT INTO `point` (`point_id`, `student_id`, `subject`, `score`) VALUES
    (1, 1, 'Math', 9.00),
    (2, 1, 'English', 9.00),
    (3, 2, 'Math', 8.00),
    (4, 2, 'English', 8.00),
    (5, 3, 'Math', 8.00),
    (6, 3, 'English', 8.00),
    (7, 4, 'Math', 6.00),
    (8, 4, 'English', 6.00),
    (9, 5, 'Math', 5.00),
    (10, 5, 'English', 5.00);

-- Dumping structure for table demo.student
CREATE TABLE IF NOT EXISTS `student` (
  `student_id` int NOT NULL,
  `student_name` varchar(50) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table demo.student: ~5 rows (approximately)
INSERT INTO `student` (`student_id`, `student_name`, `age`) VALUES
    (1, 'Alice', 20),
    (2, 'Bob', 22),
    (3, 'Charlie', 21),
    (4, 'David', 23),
    (5, 'Eva', 20);

 Thank you