Chapter 20: Advanced Topics (Optional)
These topics are what separate “good MySQL users” from “excellent, modern, production-ready developers”. They are heavily used in real-world applications (especially in analytics, reporting, APIs, and large-scale systems).
We’ll cover each topic with clear explanations, real-life use cases, and practical examples you can try right now.
1. Window Functions
(ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG, NTILE, etc.)
Window functions let you perform calculations across a set of rows that are related to the current row — without collapsing the result (unlike GROUP BY).
Key idea: You get the power of aggregates, but every row keeps its identity.
Most useful window functions:
| Function | What it does | Typical Use Case |
|---|---|---|
| ROW_NUMBER() | Unique sequential number (1,2,3…) | Ranking, pagination, removing duplicates |
| RANK() | Ranking with gaps after ties (1,2,2,4…) | Competition rankings |
| DENSE_RANK() | Ranking without gaps (1,2,2,3…) | Leaderboards |
| LEAD() | Value from next row | Compare current vs next value |
| LAG() | Value from previous row | Compare current vs previous value |
Example – Student ranking by join date per gender
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT full_name, gender, join_date, ROW_NUMBER() OVER (ORDER BY join_date) AS overall_row_num, ROW_NUMBER() OVER (PARTITION BY gender ORDER BY join_date) AS rank_in_gender, RANK() OVER (PARTITION BY gender ORDER BY join_date) AS rank_with_gaps, DENSE_RANK() OVER (PARTITION BY gender ORDER BY join_date) AS dense_rank, LAG(join_date) OVER (PARTITION BY gender ORDER BY join_date) AS previous_join, LEAD(join_date) OVER (PARTITION BY gender ORDER BY join_date) AS next_join FROM students ORDER BY gender, join_date; |
Real-world use case: “Show the 3 most recently joined students in each city”
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY city ORDER BY join_date DESC) AS rn FROM students ) t WHERE rn <= 3; |
2. Common Table Expressions (CTE) – WITH clause
CTE = temporary named result set that you can reference multiple times in the same query. Much cleaner than subqueries, and allows recursive queries.
Syntax:
|
0 1 2 3 4 5 6 7 8 9 |
WITH cte_name AS ( SELECT ... ) SELECT ... FROM cte_name; |
Example 1 – Simple CTE: Total enrollments per student
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
WITH enrollment_counts AS ( SELECT student_id, COUNT(*) AS total_courses, SUM(fees_paid) AS total_paid FROM enrollments GROUP BY student_id ) SELECT s.full_name, e.total_courses, e.total_paid, (SELECT SUM(fees) FROM courses WHERE course_id IN ( SELECT course_id FROM enrollments WHERE student_id = s.student_id )) AS total_fees FROM students s LEFT JOIN enrollment_counts e ON s.student_id = e.student_id; |
Example 2 – Recursive CTE: Generate date series (very useful for reports)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
WITH RECURSIVE dates AS ( SELECT DATE('2025-01-01') AS report_date UNION ALL SELECT DATE_ADD(report_date, INTERVAL 1 DAY) FROM dates WHERE report_date < '2025-01-31' ) SELECT report_date FROM dates; |
→ Creates 31 rows — perfect for daily reports, missing data filling, etc.
3. JSON Data Type & Functions (MySQL 5.7+ / 8.0+)
MySQL supports native JSON column type — very powerful for semi-structured data (API responses, config, logs).
Create table with JSON:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE student_profiles ( profile_id INT AUTO_INCREMENT PRIMARY KEY, student_id INT, extra_info JSON ); INSERT INTO student_profiles (student_id, extra_info) VALUES (1, '{"hobbies": ["cricket", "reading"], "address": {"city": "Mumbai", "pin": 400001}, "skills": ["Maths", "Python"]}'), (2, '{"hobbies": ["football"], "address": {"city": "Pune", "pin": 411001}}'); |
Most useful JSON functions:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- Extract value (→ scalar, →> unquoted) SELECT extra_info->>'$.address.city' AS city FROM student_profiles; -- Check if key exists SELECT * FROM student_profiles WHERE JSON_CONTAINS(extra_info, '"cricket"', '$.hobbies'); -- Add new key UPDATE student_profiles SET extra_info = JSON_SET(extra_info, '$.phone', '9876543210') WHERE student_id = 1; -- Merge JSON SELECT JSON_MERGE_PATCH(extra_info, '{"favorite_color": "blue"}') FROM student_profiles; |
Real use case: Store flexible user preferences without schema changes.
4. Full-Text Search
Much faster and smarter than LIKE ‘%word%’ for large text columns.
Requirements:
- InnoDB or MyISAM table
- Column must have FULLTEXT index
Create fulltext index:
|
0 1 2 3 4 5 6 |
ALTER TABLE students ADD FULLTEXT(full_name, notes); |
Basic full-text search:
|
0 1 2 3 4 5 6 7 8 |
SELECT full_name, notes FROM students WHERE MATCH(full_name, notes) AGAINST('regular' IN NATURAL LANGUAGE MODE); |
Boolean mode (more control):
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
-- Must have "extra" and may have "classes" SELECT * FROM students WHERE MATCH(notes) AGAINST('+extra -classes' IN BOOLEAN MODE); -- Exact phrase WHERE MATCH(notes) AGAINST('"extra classes"' IN BOOLEAN MODE); |
Scoring (relevance):
|
0 1 2 3 4 5 6 7 8 9 10 |
SELECT full_name, MATCH(notes) AGAINST('regular student') AS relevance FROM students WHERE MATCH(notes) AGAINST('regular student') ORDER BY relevance DESC; |
5. Table Partitioning (for very large tables)
Partitioning splits one large table into smaller, more manageable pieces (physically separate files).
Most common types:
- RANGE (by date, id range)
- LIST (by specific values)
- HASH / KEY (even distribution)
Example – Partition students by join_year (RANGE)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE TABLE students_partitioned ( student_id INT AUTO_INCREMENT, full_name VARCHAR(100), join_date DATE, PRIMARY KEY (student_id, join_date) ) PARTITION BY RANGE (YEAR(join_date)) ( PARTITION p2024 VALUES LESS THAN (2025), PARTITION p2025 VALUES LESS THAN (2026), PARTITION p2026 VALUES LESS THAN (2027), PARTITION p_future VALUES LESS THAN MAXVALUE ); |
Advantages:
- Faster DELETE/TRUNCATE of old partitions
- Better query performance on partitioned columns
- Easier archiving
Caution:
- Primary key must include partition key
- Some limitations on foreign keys, unique indexes
That’s the end of our Advanced Topics chapter — and the complete MySQL Tutorial series! 🎉
You now have knowledge that covers beginner → intermediate → advanced/professional level MySQL.
Quick final homework (optional but recommended): Try one of these mini-projects:
- Write a query using CTE + window function to show top 3 students per gender by join date
- Create a table with JSON column for student preferences and query it
- Add a FULLTEXT index to notes and search for words
If you want:
- Full practice project (e.g., complete school management system)
- Advanced interview questions
- Performance tuning deep dive
- Replication / High Availability
