10 Must‑Know MySQL Advanced Techniques to Supercharge Performance

Preface

Many developers only use MySQL for basic CRUD, missing the advanced features that can solve most complex performance problems.

This article dives into ten practical MySQL techniques for high‑performance, high‑concurrency, and large‑data scenarios.

1. Execution Plan (EXPLAIN)

Understanding the output of EXPLAIN is the first step to query optimization; it shows how MySQL intends to execute a query and where bottlenecks lie.

Key fields to examine

type : access type, ordered from best to worst (system, const, eq_ref, ref, range, index, ALL). Seeing ALL indicates a full table scan.

key : the index actually used; NULL means no index.

rows : estimated rows examined; lower is better.

Extra : additional info such as Using filesort or Using temporary, which are performance killers.

Example of a slow query and its EXPLAIN output:

-- A query that needs optimization
EXPLAIN
SELECT *
FROM orders
WHERE user_id = 10086
  AND create_time BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY amount DESC;

If the type is ALL and key is NULL, the optimizer is scanning the whole orders table. Creating a covering composite index fixes it:

-- Create a composite index covering WHERE and ORDER BY
CREATE INDEX idx_user_time_amount
ON orders(user_id, create_time, amount);

After rebuilding the index, EXPLAIN shows type changes to range and the new index is used.

EXPLAIN relies on table statistics; if they are stale, run ANALYZE TABLE table_name; to refresh.

2. Advanced Index Strategies

Indexes are the foundation of performance, but a wrong index can be worse than none.

Covering Index

If an index contains all columns needed by a query, the engine can satisfy the query from the index alone, avoiding a table lookup.

-- Example: query only needs name and email
SELECT name, email
FROM users
WHERE age > 20;

-- Covering index for the query
CREATE INDEX idx_age_name_email
ON users(age, name, email);

Index Condition Pushdown (ICP)

Introduced in MySQL 5.6, ICP pushes predicates on the second column of a composite index down to the storage engine, reducing the number of rows that need to be read.

-- Example: WHERE a = ? AND b LIKE '%xxx%'
-- Without ICP, MySQL reads rows matching a, then filters b.
-- With ICP, the b condition is applied during index scan.

Prefix Index

For very long VARCHAR columns, indexing only the first N characters saves space while still providing useful selectivity.

-- Prefix index on first 100 characters of content
CREATE INDEX idx_content_prefix
ON articles (content(100));

Prefix indexes cannot be used for GROUP BY or ORDER BY.

Regularly audit indexes with SHOW INDEX FROM table_name; and drop low‑cardinality or unused indexes.

3. Window Functions

MySQL 8.0 introduced window functions, enabling row‑wise calculations such as ranking, cumulative sums, and moving averages without self‑joins.

SELECT
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_salary_rank,
    RANK() OVER (ORDER BY salary DESC) AS company_rank,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    salary / SUM(salary) OVER (PARTITION BY department) AS salary_ratio
FROM employees;

The PARTITION BY clause works like GROUP BY but keeps rows separate, defining the window scope.

Window functions dramatically simplify analytics and OLAP‑style reporting.

4. Common Table Expressions (CTE)

The WITH clause lets you define temporary named result sets that can be referenced later, improving readability and supporting recursion.

Non‑recursive example

WITH high_value_orders AS (
    SELECT user_id, SUM(amount) AS total_spent
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
    HAVING total_spent > 10000
), active_users AS (
    SELECT DISTINCT user_id
    FROM user_logs
    WHERE last_active_date > DATE_SUB(NOW(), INTERVAL 30 DAY)
)
SELECT u.name, u.email, h.total_spent
FROM users u
JOIN high_value_orders h ON u.id = h.user_id
JOIN active_users a ON u.id = a.user_id;

Recursive example (hierarchical data)

WITH RECURSIVE department_tree AS (
    SELECT id, name, parent_id, 1 AS level
    FROM departments
    WHERE parent_id IS NULL
    UNION ALL
    SELECT d.id, d.name, d.parent_id, dt.level + 1
    FROM departments d
    JOIN department_tree dt ON d.parent_id = dt.id
)
SELECT *
FROM department_tree
ORDER BY level, id;

Recursive CTEs replace complex stored procedures for tree or graph traversals.

5. JSON Data Type and Functions

MySQL 5.7+ supports a native JSON column, allowing flexible storage of semi‑structured data such as dynamic attributes or API responses.

-- Create a table with a JSON column
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    attributes JSON COMMENT 'stores color, size, tags'
);

-- Insert JSON data
INSERT INTO products VALUES
(1, 'T-Shirt', '{"color":"red","size":["M","L"],"tags":["casual","cotton"]}');

-- Query using -> and ->> operators
SELECT
    name,
    attributes->>'$.color' AS color,
    attributes->'$.size' AS size_array
FROM products
WHERE attributes->>'$.color' = 'red'
   OR JSON_CONTAINS(attributes->'$.tags', '"cotton"');

Partial updates are possible with JSON_SET:

UPDATE products
SET attributes = JSON_SET(attributes,
                         '$.color', 'blue',
                         '$.new_field', 'value')
WHERE id = 1;

Function‑based indexes can accelerate JSON queries:

CREATE INDEX idx_color
ON products ((attributes->>'$.color'));

6. Partitioned Tables

When a single table reaches hundreds of millions of rows, partitioning splits it into physically separate pieces while preserving a logical single‑table view.

-- Range partitioning by sale_date
CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE COLUMNS(sale_date) (
    PARTITION p2023q1 VALUES LESS THAN ('2023-04-01'),
    PARTITION p2023q2 VALUES LESS THAN ('2023-07-01'),
    PARTITION p2023q3 VALUES LESS THAN ('2023-10-01'),
    PARTITION p2023q4 VALUES LESS THAN ('2024-01-01'),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

Queries automatically prune irrelevant partitions, e.g.:

EXPLAIN SELECT * FROM sales WHERE sale_date = '2023-05-15';
-- Planner shows only partition p2023q2 is scanned.

Other partition types include LIST and HASH. Dropping or archiving old partitions is fast with ALTER TABLE ... DROP PARTITION, but a poor partition key can degrade performance.

7. JOIN and Subquery Optimizations

Multi‑table joins are common but can become performance disasters if not tuned.

Force join order

SELECT ...
FROM small_table s
STRAIGHT_JOIN large_table l ON s.id = l.s_id;  -- forces small table first

Derived table filtering

-- Bad: join then filter
SELECT *
FROM A
JOIN B ON A.id = B.aid
WHERE A.create_time > '...';

-- Good: filter A first
SELECT *
FROM (SELECT * FROM A WHERE create_time > '...') filtered_A
JOIN B ON filtered_A.id = B.aid;

EXISTS vs IN

For existence checks, EXISTS stops after the first match and is usually faster than IN on large subqueries.

Overall principle: “filter early, reduce intermediate result size”. Use EXPLAIN to verify join types ( eq_ref, Using join buffer, etc.).

8. User‑Defined Variables

Before MySQL 8.0, user variables (e.g., @rank) allowed row‑wise calculations that mimic window functions.

-- Calculate daily growth rate using variables
SELECT
    sale_date,
    daily_amount,
    @prev_amount AS prev_day_amount,
    ROUND((daily_amount - @prev_amount) / @prev_amount * 100, 2) AS growth_rate,
    @prev_amount := daily_amount
FROM daily_sales_summary,
     (SELECT @prev_amount := 0) init
ORDER BY sale_date;

Variables are not standard SQL; their evaluation order can be unintuitive, and their scope is session‑wide.

9. Online DDL and Lock‑Free Changes

For 24/7 services, altering large tables must avoid downtime. MySQL 5.6+ provides ALGORITHM and LOCK clauses.

-- Add a nullable column with default, using INPLACE and no lock
ALTER TABLE huge_table
ADD COLUMN new_column VARCHAR(100) DEFAULT '' NOT NULL,
ALGORITHM=INPLACE,
LOCK=NONE;

-- Change column type (may require COPY and a shared lock)
ALTER TABLE huge_table
MODIFY COLUMN old_column BIGINT,
ALGORITHM=COPY,
LOCK=SHARED;

ALGORITHM=INPLACE

rebuilds indexes internally and permits concurrent DML; ALGORITHM=COPY creates a new table and locks it. Test with ALGORITHM=DEFAULT first.

Percona’s pt-online-schema-change uses triggers to achieve truly lock‑free schema changes.

10. Generated Columns and Functional Indexes

Generated columns compute their values from other columns. They can be VIRTUAL (computed on read) or STORED (materialized).

-- Add a stored generated column for full name and index it
ALTER TABLE users
ADD COLUMN full_name VARCHAR(255)
GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) STORED,
ADD INDEX idx_full_name (full_name);

-- Query can now use the index
SELECT * FROM users WHERE full_name = 'John Doe';

Functional indexes on expressions (e.g., JSON extraction) are built on generated columns or directly on the expression in newer MySQL versions.

Virtual columns save space but add CPU overhead; stored columns trade space for read speed.

Conclusion

The ten techniques form a toolbox for tackling diverse MySQL performance challenges—from query planning and indexing to modern features like window functions, CTEs, JSON handling, partitioning, online DDL, and generated columns.

Start by mastering EXPLAIN and index design, then progressively adopt window functions or CTEs for complex analytics, and consider partitioning or online schema change when data volume grows.