10 Must‑Know MySQL Advanced Techniques to Supercharge Performance

1. Introduction

Many developers only use MySQL for basic CRUD operations and miss its powerful advanced features that can solve complex business problems efficiently. This guide focuses on ten practical, high‑level MySQL techniques that improve development speed and system performance in large‑scale, high‑concurrency scenarios.

2. Practical Cases

2.1 EXPLAIN Execution Plan

Before optimizing any query, run EXPLAIN to view how MySQL intends to execute it. Key fields to examine are:

type : access type, ordered from best to worst (system > const > eq_ref > ref > range > index > ALL).

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

rows : estimated rows scanned; the closer to the actual row count, the better.

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

Example of a poorly performing query (type = ALL) and its optimized version using a composite index:

CREATE INDEX idx_user_time_amount ON orders (user_id, create_time, amount);

After creating the index, the execution plan shows a more selective type and eliminates Using filesort. Further refinement by reordering columns in the index can remove the filesort entirely:

CREATE INDEX idx_user_time_amount ON orders (user_id, amount, create_time);

2.2 Advanced Index Strategies

Three advanced index techniques are covered:

Covering Index : When the index contains all columns needed by the query, the engine can retrieve results directly from the index without a table lookup.

-- Query: select name, email where age > 20
SELECT name, email FROM users WHERE age > 20;
-- Covering index
CREATE INDEX idx_age_name_email ON users(age, name, email);

Index Condition Push‑Down (ICP) : Introduced in MySQL 5.6, ICP pushes filter conditions down to the storage engine, reducing row‑by‑row processing. Example with a composite index (a, b) and a LIKE condition on b demonstrates the difference between having ICP on or off.

-- Enable ICP
SET optimizer_switch = 'index_condition_pushdown=on';
-- Disable ICP
SET optimizer_switch = 'index_condition_pushdown=off';

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

CREATE INDEX idx_content_prefix ON articles (content(100));

Note: Prefix indexes cannot be used for GROUP BY or ORDER BY operations.

2.3 Window Functions

MySQL 8.0 adds window functions for row‑wise calculations such as ranking, cumulative sums, and ratios. Example: ranking salaries within each department and calculating each employee’s salary share.

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;

2.4 Common Table Expressions (CTE)

CTEs ( WITH clause) improve query readability and enable recursion. Non‑recursive example splits a complex query into logical steps; recursive example builds a department hierarchy.

-- Non‑recursive CTE
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 CTE for department tree
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;

2.5 JSON Type and Functions

MySQL 5.7+ supports native JSON columns. The guide shows how to create a table with a JSON field, query using -> and ->> operators, and update parts of a JSON document.

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  attributes JSON COMMENT 'stores dynamic attributes like color, size'
);
SELECT name, attributes->>'$.color' AS color,
       attributes->'$.size' AS size_array
FROM products
WHERE attributes->>'$.color' = 'red'
   OR JSON_CONTAINS(attributes->'$.tags', '"cotton"');
UPDATE products SET attributes = JSON_SET(attributes, '$.color', 'blue', '$.new_field', 'value')
WHERE id = 1;

Function indexes can accelerate JSON queries by casting extracted values to a fixed‑length type:

CREATE INDEX idx_color ON products ((CAST(attributes->>'$.color' AS CHAR(50)));

2.6 Partitioned Tables

For tables with billions of rows, partitioning splits the data physically while keeping a logical single table. Example partitions a sales table by sale_date range:

CREATE TABLE sales (
  id INT,
  sale_date DATE,
  amount DECIMAL(10,2)
)
PARTITION BY RANGE COLUMNS(sale_date) (
  PARTITION p2025q1 VALUES LESS THAN ('2025-04-01'),
  PARTITION p2025q2 VALUES LESS THAN ('2025-07-01'),
  PARTITION p2025q3 VALUES LESS THAN ('2025-10-01'),
  PARTITION p2025q4 VALUES LESS THAN ('2026-01-01'),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

2.7 JOIN and Subquery Optimizations

Control Join Order : Use STRAIGHT_JOIN to force the optimizer to read the smaller table first.

SELECT ... FROM small_table s STRAIGHT_JOIN large_table l ON s.id = l.s_id;

Derived Tables for Predicate Push‑Down : Filter early to reduce intermediate result size.

SELECT * FROM (
  SELECT * FROM A WHERE create_time > '...'
) filtered_A JOIN B ON filtered_A.id = B.aid;

EXISTS vs IN : EXISTS stops after the first match and is usually faster for large subqueries.

2.8 User‑Defined Variables

Variables such as @prev_amount enable row‑by‑row calculations, e.g., daily sales growth rate.

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;

2.9 Online DDL and Lock‑Free Changes

MySQL 5.6+ supports online DDL with ALGORITHM=INPLACE and LOCK=NONE to avoid table locks. Example adds a nullable column with a default value and later modifies a column type.

ALTER TABLE huge_table ADD COLUMN new_column VARCHAR(100) DEFAULT '' NOT NULL,
  ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE huge_table MODIFY COLUMN old_column BIGINT,
  ALGORITHM=COPY, LOCK=SHARED;

2.10 Generated Columns and Functional Indexes

Generated columns compute values from other columns and can be indexed for fast lookup. Example creates a stored generated column full_name and indexes 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);
SELECT * FROM users WHERE full_name = 'John Doe';

These ten techniques collectively enable developers to extract the full performance potential of MySQL in demanding applications.