Advanced MySQL Query Techniques

Most developers interact with MySQL through familiar CRUD operations — simple SELECT, INSERT, and UPDATE statements.
But MySQL 8 introduced a new set of powerful features that can handle advanced analytical use cases, hierarchical data, and complex aggregations directly within SQL.

In this guide, we’ll explore practical, non-standard but extremely useful query patterns, all using plain MySQL 8+.
Each section includes example tables, real-world use cases, and a short explanation of how and why the query works.

💡 Note: All queries in this article require MySQL 8.0 or higher (due to CTEs, window functions, and JSON features).

---

🧱 Sample Schema

We'll use two simple tables to simulate a business environment — employees with managers, and their sales records.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    sale_date DATE,
    amount DECIMAL(10,2),
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

INSERT INTO employees (name, manager_id) VALUES
('Alice', NULL),
('Bob', 1),
('Charlie', 2),
('Diana', 2),
('Eve', 3);

INSERT INTO sales (employee_id, sale_date, amount) VALUES
(1, '2024-01-15', 1200.00),
(2, '2024-02-10', 900.00),
(3, '2024-02-18', 400.00),
(4, '2024-03-05', 1000.00),
(5, '2024-03-20', 700.00);

---

🧩 1. Recursive CTE for Parent–Child Hierarchies

Managing hierarchies like employees → managers or categories → subcategories used to require multiple self-joins.
With recursive CTEs (Common Table Expressions), it’s elegant and intuitive.

WITH RECURSIVE employee_hierarchy AS (
  SELECT id, name, manager_id
  FROM employees
  WHERE id = 1  -- starting point (Alice)
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

💬 What it does:
Finds all employees under Alice recursively — great for visualizing an org chart or exploring category trees.

📈 Real Use Case:

• E-commerce category hierarchies (e.g. Electronics → Computers → Laptops)
• Corporate organizational charts
• Forum threads or comment trees

---

🧮 2. Partition Queries for Large Datasets

As tables grow into millions of rows, performance drops dramatically.
Table partitioning splits data into smaller logical chunks, letting MySQL read only what it needs.

CREATE TABLE sales_partitioned (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sale_date DATE,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

SELECT
    YEAR(sale_date) AS sale_year,
    SUM(amount) AS total_sales
FROM sales_partitioned
GROUP BY sale_year;

💬 What it does:
Divides data into yearly partitions — making queries on specific years (like 2024) much faster.

⚙️ Common Partition Types:

• RANGE — useful for dates or sequential IDs
• HASH — distributes evenly across partitions
• LIST — custom groupings (e.g., region or product category)

---

📊 3. Window Functions for Ranking and Analytics

Window functions allow complex calculations without losing row-level detail.
Perfect for generating rankings, cumulative totals, or comparisons.

SELECT
    employee_id,
    SUM(amount) AS total_sales,
    RANK() OVER (ORDER BY SUM(amount) DESC) AS rank_position,
    ROUND(
        SUM(amount) / SUM(SUM(amount)) OVER () * 100, 2
    ) AS percent_of_total
FROM sales
GROUP BY employee_id;

💬 What it does:
Ranks employees by total sales and shows what percentage of total revenue each one contributes.

🧠 Common Window Functions:

• RANK() – gives ranking with ties
• DENSE_RANK() – no gaps in rank numbers
• ROW_NUMBER() – unique sequence for each row
• LAG() / LEAD() – compare current row with previous/next one
• NTILE() – divides data into buckets (e.g., quartiles)

---

🧠 4. JSON Functions for Semi-Structured Data

JSON columns let you store flexible attributes without altering table schemas.
MySQL 8 adds rich functions for querying and manipulating them.

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_data JSON
);

INSERT INTO orders (order_data) VALUES
('{"customer": "Alice", "items": [{"product": "Laptop", "qty": 1}]}'),
('{"customer": "Bob", "items": [{"product": "Mouse", "qty": 2}, {"product": "Keyboard", "qty": 1}]}');

SELECT
    JSON_EXTRACT(order_data, '$.customer') AS customer,
    JSON_LENGTH(JSON_EXTRACT(order_data, '$.items')) AS item_count,
    JSON_EXTRACT(order_data, '$.items[0].product') AS first_item
FROM orders;

💬 What it does:
Extracts specific fields from JSON data — such as customer name, item count, or nested product info.

🧩 Common JSON Functions:

• JSON_EXTRACT() → Read values
• JSON_SET() → Modify values
• JSON_REMOVE() → Delete keys
• JSON_CONTAINS() → Search for specific elements

🪄 Tip: Use -> and ->> operators as shorthand for extraction:

SELECT order_data->>'$.customer' AS customer FROM orders;

---

🔄 5. Dynamic Pivot Query (Monthly Sales Summary)

MySQL doesn’t provide a native PIVOT function, but you can easily simulate it using CASE statements.

SELECT
    employee_id,
    SUM(CASE WHEN MONTH(sale_date) = 1 THEN amount ELSE 0 END) AS Jan,
    SUM(CASE WHEN MONTH(sale_date) = 2 THEN amount ELSE 0 END) AS Feb,
    SUM(CASE WHEN MONTH(sale_date) = 3 THEN amount ELSE 0 END) AS Mar
FROM sales
GROUP BY employee_id;

💬 What it does:
Shows total sales per month for each employee.
Ideal for quick reporting dashboards or Excel exports.

🧮 Bonus – Dynamic Pivot via Prepared Statement:
If months are dynamic, build the column list programmatically in SQL or your application layer using GROUP_CONCAT.

---

⚙️ 6. Performance and Maintainability Tips

✅ Use EXPLAIN before optimizing manually.
It reveals index usage, partition pruning, and CTE materialization.

✅ Index wisely.
Indexes speed up lookups, but too many slow down writes.

✅ Leverage generated columns
For JSON or computed values you query often:

ALTER TABLE orders ADD customer_name VARCHAR(100) 
    GENERATED ALWAYS AS (JSON_UNQUOTE(order_data->'$.customer')) STORED;

✅ Use ANALYZE TABLE periodically to update optimizer statistics.

✅ Avoid unnecessary recursion depth for large hierarchies — it can blow up query cost quickly.

---

🧭 Final Notes

These advanced MySQL 8+ features — CTEs, window functions, partitioning, and JSON functions — can help you write cleaner, faster, and more expressive queries.
They not only reduce application-side logic but also unlock analytical power directly within the database.

If you find yourself writing complex loops in code to process hierarchical or aggregated data, chances are MySQL can already do it better natively.