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.
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.
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.
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).
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.
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.
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.
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.
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
