Unlock MySQL 8.0: 8 Hidden Optimization Tricks You Must Know

MySQL 8.0 introduces a suite of revolutionary features that go far beyond classic optimization tactics such as basic indexing, avoiding full‑table scans, and join ordering. Developers still using MySQL 5.7‑era thinking miss out on these capabilities, which can both boost performance and impress interviewers.

Quick Review of Classic Techniques

Index optimization : B‑tree, composite, covering indexes

Query rewriting : avoid SELECT *, use appropriate LIMIT Join optimization : choose INNER JOIN vs LEFT JOIN, drive table selection

Subquery optimization : prefer EXISTS over IN, eliminate correlated subqueries

Table design : proper column types, normalization vs denormalization

Trick 1 – Window Functions for Ranking

Traditional ranking often requires nested subqueries that scan the table multiple times. MySQL 8.0’s window functions solve this with 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;

-- Window‑function approach (single scan)
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 costly filesort operations in ordered queries, especially for pagination.

CREATE INDEX idx_mixed ON orders (status ASC, created_time DESC);
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, turning slow JSON lookups into fast indexed searches.

ALTER TABLE user_profiles
ADD COLUMN age_generated INT GENERATED ALWAYS AS (JSON_EXTRACT(profile_data, '$.age')) STORED,
ADD INDEX idx_age (age_generated);

SELECT * FROM user_profiles WHERE age_generated BETWEEN 25 AND 35;

Trick 4 – Invisible Index for Safe Testing

Invisible indexes let you create an index without affecting the optimizer until you explicitly enable it, allowing risk‑free testing in production.

CREATE INDEX idx_user_age ON users (age) INVISIBLE;
SET SESSION optimizer_switch='use_invisible_indexes=on';
EXPLAIN SELECT * FROM users WHERE age BETWEEN 25 AND 35;
-- If the plan is good, make it visible:
ALTER INDEX idx_user_age ON users VISIBLE;
-- Otherwise drop it without impact:
DROP INDEX idx_user_age ON users;

Trick 5 – Optimizer Hints to Force Plans

When the optimizer picks a sub‑optimal plan, hints can force a specific join order or index usage.

-- 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 particular index
SELECT /*+ INDEX(o idx_status) */ *
FROM orders o
WHERE o.status = 'pending';

Trick 6 – Resource Groups for Query‑Level Control

Resource groups let you assign CPU and priority limits to specific workloads, separating online traffic from heavy analytical queries.

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

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

-- Use the batch group for a large aggregation
SET RESOURCE GROUP batch_group;
SELECT COUNT(*), AVG(amount) FROM orders WHERE created_time >= '2023-01-01';

-- Online queries automatically use 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.

-- Traditional, verbose version
SELECT u.username, u.email,
  (SELECT product_name FROM orders o1 JOIN products p1 ON o1.product_id = p1.id
   WHERE o1.user_id = u.id ORDER BY o1.created_time DESC LIMIT 1) AS latest_product,
  ...
FROM users u;

-- Concise LATERAL JOIN version
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 Index for JSON Arrays

MySQL 8.0.17 adds multi‑valued indexes, enabling efficient searches inside JSON arrays.

CREATE INDEX idx_tags ON articles ((CAST(tags->'$[*]' AS CHAR(50)) ARRAY));
SELECT * FROM articles WHERE JSON_OVERLAPS(tags, '["技术", "MySQL"]');

Adopting these MySQL 8.0 features can dramatically improve query speed, reduce code complexity, and give you a competitive edge in interviews and real‑world projects. Use them judiciously—simple indexes still solve many problems, but the new tools are invaluable for the toughest cases.