10 MySQL Advanced Techniques to Supercharge Performance

Execution Plan (EXPLAIN)

Before optimizing any query, run EXPLAIN to see how MySQL will execute it. Important columns are:

type : access method, from system (best) to ALL (full table scan).

key : the index used; NULL means no index.

rows : estimated rows examined.

Extra : additional notes such as Using filesort or Using temporary.

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);

Advanced Index Strategies

Covering Index

A covering index contains all columns required by the query, allowing the engine to satisfy the query directly from the index without touching the table.

SELECT name, email
FROM users
WHERE age > 20;

CREATE INDEX idx_age_name_email
ON users(age, name, email);

Index Condition Pushdown (ICP)

Introduced in MySQL 5.6, ICP pushes filter predicates down to the storage engine during an index scan, reducing the number of rows that need to be read from the table.

Prefix Index

For very long VARCHAR columns, indexing only the first N characters saves space and improves insert performance.

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

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

Window Functions (MySQL 8.0+)

Window functions enable 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;

Common Table Expressions (CTE)

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 (department hierarchy)

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;

JSON Data Type and Functions

MySQL 5.7+ supports a native JSON column type for semi‑structured data.

-- 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 (id, name, attributes)
VALUES (1, 'T-Shirt', '{"color":"red","size":["M","L"],"tags":["casual","cotton"]}');

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

-- Functional index on a JSON attribute
CREATE INDEX idx_color ON products ((attributes->>'$.color'));

Partitioned Tables

Partitioning splits a huge logical table into smaller physical pieces, improving maintenance and query performance when the partition key is used.

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)
);

-- Partition pruning example
EXPLAIN SELECT * FROM sales WHERE sale_date = '2023-05-15';

Choose the partition key carefully; a poor choice can degrade performance.

JOIN and Subquery Optimizations

Control join order : use STRAIGHT_JOIN to force the optimizer to read the small table first.

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

Derived tables (sub‑queries) to push filters early :

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

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

User‑Defined Variables

Variables such as @prev_amount allow row‑by‑row calculations in versions prior to window functions.

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.

Online DDL and Lock‑Free Changes

Use ALGORITHM and LOCK clauses to perform schema changes without blocking reads/writes.

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

ALGORITHM=INPLACE

rebuilds indexes internally; ALGORITHM=COPY creates a new table and locks it. For truly lock‑free migrations, tools like pt-online-schema-change use triggers to copy data in the background.

Generated Columns and Functional Indexes

Generated columns compute values from other columns and can be indexed. They come in two flavors:

VIRTUAL : computed on read.

STORED : materialized and indexable.

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);

Queries that filter on full_name now use the index, solving the problem of indexing expressions directly.