Unlock MySQL 8.0’s Hidden Optimization Tricks to Supercharge Your Queries

Introduction

Most developers still think of classic SQL‑optimization tactics such as adding indexes, avoiding full‑table scans, and reordering joins. While those basics are still relevant, they belong to the MySQL 5.7 era. MySQL 8.0 introduces many revolutionary features that can dramatically boost performance and impress interviewers.

Quick Review of Traditional Techniques

Index optimization: B+Tree indexes, composite indexes, covering indexes

Query rewriting: avoid SELECT *, use LIMIT wisely

Join optimization: choose between INNER JOIN and LEFT JOIN based on driver table

Subquery optimization: prefer EXISTS over IN to avoid correlated subqueries

Table design: proper column types, normalization vs. denormalization

Hidden MySQL 8.0 Optimizations

Trick 1: Window Functions for Complex Queries

Traditional grouping often requires multiple subqueries and repeated scans. MySQL 8.0’s window functions can compute rankings in a single pass.

-- Traditional approach (multiple scans)
SELECT *
FROM employees e1
WHERE (
  SELECT COUNT(DISTINCT e2.salary)
  FROM employees e2
  WHERE e2.department = e1.department
    AND e2.salary >= e1.salary
) <= 3;

Using a window function reduces the scan to one.

WITH ranked_employees AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT *
FROM ranked_employees
WHERE rn <= 3;

Trick 2: Descending Indexes

MySQL 8.0 supports true descending indexes, which can eliminate filesort in certain ORDER BY scenarios.

-- Create a mixed‑order index
CREATE INDEX idx_mixed ON orders (status ASC, created_time DESC);

-- Query that fully uses the index order
SELECT *
FROM orders
WHERE status = 'pending'
ORDER BY created_time DESC
LIMIT 10;

Trick 3: Generated Columns for JSON

Generated (virtual) columns can store computed values from JSON fields and be indexed for fast retrieval.

-- Add a generated column and index it
ALTER TABLE user_profiles
ADD COLUMN age_generated INT GENERATED ALWAYS AS (JSON_EXTRACT(profile_data, '$.age')) STORED,
ADD INDEX idx_age (age_generated);

-- Query using the generated column
SELECT *
FROM user_profiles
WHERE age_generated BETWEEN 25 AND 35;

Trick 4: Invisible Indexes for Safe Testing

Invisible indexes let you create an index without the optimizer using it, enabling safe A/B testing in production.

-- Create an invisible index
CREATE INDEX idx_user_age ON users (age) INVISIBLE;

-- Enable it for the current session
SET SESSION optimizer_switch = 'use_invisible_indexes=on';

-- Explain to see if it is used
EXPLAIN SELECT * FROM users WHERE age BETWEEN 25 AND 35;

-- Make it visible after verification
ALTER INDEX idx_user_age VISIBLE;

-- Drop if not useful
DROP INDEX idx_user_age ON users;

Trick 5: Hints to Force Execution Plans

When the optimizer chooses a sub‑optimal plan, you can force join order or index usage with optimizer hints.

-- Force small table as driver
SELECT /*+ STRAIGHT_JOIN */ *
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Shanghai' AND o.status = 'pending';

-- Force a specific index
SELECT /*+ INDEX(o idx_status) */ *
FROM orders o
WHERE o.status = 'pending';

Trick 6: Resource Groups for Query‑Level Resource Control

Resource groups let you assign CPU cores and thread priorities to different workloads, isolating heavy analytical queries from latency‑sensitive OLTP queries.

-- Batch processing group (low priority, limited CPUs)
CREATE RESOURCE GROUP batch_group TYPE = USER VCPU = 0-1 THREAD_PRIORITY = -10;

-- Online business group (high priority)
CREATE RESOURCE GROUP online_group TYPE = USER VCPU = 2-7 THREAD_PRIORITY = 0;

-- Run a heavy analytics query in the batch group
SET RESOURCE GROUP batch_group;
SELECT COUNT(*), AVG(amount)
FROM orders
WHERE created_time >= '2023-01-01';

-- Online query runs in the default (online) group
SELECT * FROM orders WHERE id = 12345;

Trick 7: LATERAL JOIN for Complex Associations

LATERAL JOIN allows the right‑hand side to reference columns from the left table, simplifying queries that would otherwise need multiple correlated subqueries.

SELECT u.username, u.email, recent_orders.*
FROM users u
JOIN LATERAL (
  SELECT p.product_name, o.created_time, o.amount
  FROM orders o
  JOIN products p ON o.product_id = p.id
  WHERE o.user_id = u.id
  ORDER BY o.created_time DESC
  LIMIT 3
) recent_orders ON TRUE;

Trick 8: Multi‑Valued Indexes for JSON Arrays

MySQL 8.0.17 adds multi‑valued indexes designed for JSON array queries, enabling fast lookups on array elements.

-- Create a multi‑valued index on a JSON array column
CREATE INDEX idx_tags ON articles ((CAST(tags->'$[*]' AS CHAR(50) ARRAY)));

-- Efficient query for articles containing specific tags
SELECT *
FROM articles
WHERE JSON_OVERLAPS(tags, '["技术", "MySQL"]');

Conclusion

The new features of MySQL 8.0 represent a new era of SQL optimization, allowing developers to write cleaner code while achieving higher performance. When applied appropriately, these techniques can solve problems that traditional methods struggle with and showcase advanced knowledge in interviews or technical talks. However, they should be used judiciously—sometimes a simple index is sufficient, and the best solution always matches the specific workload.