10 Must‑Know MySQL Advanced Techniques to Supercharge Performance

Introduction

Many developers treat MySQL as a simple data store and only use basic CRUD operations, missing the advanced features that can solve most complex performance problems.

01 Execution Plan (EXPLAIN)

Before optimizing any query, understanding the output of EXPLAIN is essential. It shows how MySQL plans to execute the query, highlighting bottlenecks.

Key fields to watch

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

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

rows : estimated rows to scan; lower is better.

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

-- Example query needing 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 plan shows type=ALL and key=NULL, create a covering composite index:

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

Rerunning EXPLAIN should change type to range and show the new index.

When statistics are stale, run ANALYZE TABLE table_name; to refresh them.

02 Advanced Index Strategies

Indexes are the foundation of performance, but misuse can be worse than no index.

Covering Index

If an index contains all columns needed by the query, the engine can retrieve rows directly from the index without accessing the table.

-- Query needing 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 non‑leading columns of a composite index down to the storage engine, reducing row‑filtering work.

SELECT *
FROM orders
WHERE a = ? AND b LIKE '%xxx%';
-- With ICP, the <code>b LIKE</code> condition is evaluated during index scan.

Prefix Index

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

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 ones.

03 Window Functions

MySQL 8.0 introduced window functions for efficient row‑wise calculations such as ranking, cumulative sums, and moving averages.

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;

Window functions replace complex self‑joins or subqueries and deliver orders‑of‑magnitude speed gains for OLAP‑style reporting.

04 Common Table Expressions (CTE)

CTEs (the WITH clause) let you break complex queries into readable steps and support 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 simplify tree or graph queries that previously required multiple round‑trips or stored procedures.

05 JSON Data Type and Functions

MySQL 5.7+ supports native JSON columns, enabling flexible storage of semi‑structured data.

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  attributes JSON COMMENT 'stores color, size, tags'
);
INSERT INTO products VALUES (1, 'T-Shirt', '{"color": "red", "size": ["M","L"], "tags": ["casual","cotton"]}');
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;

JSON columns can be indexed via functional indexes to retain query performance.

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

06 Partitioned Tables

When a single table reaches hundreds of millions of rows, partitioning splits it physically into smaller pieces while keeping a logical single table.

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)
);
EXPLAIN SELECT * FROM sales WHERE sale_date = '2023-05-15';

Partition pruning ensures only the relevant partition is scanned. Other strategies include LIST, HASH, and KEY partitions. Dropping or archiving old partitions is fast with ALTER TABLE … DROP PARTITION, but a poor partition key can degrade performance.

07 JOIN and Subquery Optimizations

Complex joins can become performance disasters; the key is to filter early and reduce intermediate result sets.

Force join order with STRAIGHT_JOIN when the optimizer chooses a sub‑optimal order.

Push predicates into derived tables to shrink data before the actual join.

Prefer EXISTS over IN for large sub‑query result sets because EXISTS stops after the first match.

-- Force small table first
SELECT ...
FROM small_table s
STRAIGHT_JOIN large_table l ON s.id = l.s_id;
-- Push filter into derived table
SELECT *
FROM (SELECT * FROM A WHERE create_time > '...') filtered_A
JOIN B ON filtered_A.id = B.aid;

Use EXPLAIN to verify join types (e.g., eq_ref) and whether a join buffer is used.

08 User‑Defined Variables

MySQL allows session variables (e.g., @rank) for row‑wise calculations, useful before window functions existed.

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 part of the SQL standard; their evaluation order can be unintuitive, so use them cautiously.

09 Online DDL (Non‑Blocking Schema Changes)

MySQL 5.6+ supports ALGORITHM and LOCK clauses to perform online DDL without stopping 24/7 services.

-- Add a nullable column with minimal locking
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

keeps most work inside the engine, allowing concurrent DML. ALGORITHM=COPY rebuilds the table and locks it. Test with ALGORITHM=DEFAULT first. Tools like pt-online-schema-change provide truly lock‑free migrations via triggers.

10 Generated Columns and Functional Indexes

Generated columns compute values from other columns. They can be VIRTUAL (computed on read) or STORED** (persisted). Stored generated columns enable functional indexes.

-- Create a stored generated column for full name
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 efficiently
SELECT * FROM users WHERE full_name = 'John Doe';

Virtual columns save space but add CPU overhead; stored columns consume space but allow fast indexed lookups.

Conclusion

The eleven techniques form a toolbox for tackling diverse performance challenges: start with EXPLAIN and index tuning, then adopt window functions or CTEs for complex analytics, use partitioning for massive tables, leverage JSON for flexible schemas, and apply online DDL or generated columns for safe schema evolution.