10 Advanced MySQL Techniques to Supercharge Query Performance

1. Execution Plan (EXPLAIN)

Before optimizing any query, read the EXPLAIN output. Important fields are type (access method order from system to ALL), key (used index, NULL means none), rows (estimated rows), and Extra (e.g., Using filesort, Using temporary). An example query that scans the whole orders table (type ALL, key NULL) is fixed by creating a composite index on (user_id, create_time, amount), after which EXPLAIN shows type=range and the new index.

2. Advanced Index Strategies

Covering indexes store all columns needed by a query, eliminating the need for a table lookup. Index Condition Pushdown (ICP) introduced in MySQL 5.6 pushes predicates on the second column of a composite index down to the storage engine, reducing row‑return overhead. Prefix indexes index only the first N characters of long VARCHAR columns, saving space at the cost of some query capabilities.

3. Window Functions

MySQL 8.0 adds window functions for row‑wise calculations such as ranking, cumulative sums, and moving averages. Example: ranking salaries per department using RANK() OVER (PARTITION BY department ORDER BY salary DESC). These functions replace complex self‑joins and subqueries, delivering order‑of‑magnitude speedups for OLAP‑style reports.

4. Common Table Expressions (CTE)

CTEs improve readability by breaking complex queries into named sub‑queries. A non‑recursive CTE can simplify aggregation, while a recursive CTE can traverse hierarchical data such as department trees. The optimizer treats the CTE as an inline view, allowing better planning.

5. JSON Type and Functions

MySQL 5.7+ supports a native JSON column type. You can insert JSON documents, extract values with -> (JSON) and ->> (text), and query with JSON_CONTAINS. Function indexes on JSON paths (e.g., CREATE INDEX idx_color ON products((attributes->>'$.color'))) keep query performance high while storing semi‑structured data.

6. Partitioning

Partitioning splits massive tables into smaller physical pieces. RANGE partitioning by date is ideal for logs or orders. Queries automatically prune irrelevant partitions, dramatically reducing I/O. Other methods include LIST and HASH. Careful partition‑key selection is essential to avoid performance regressions.

7. JOIN and Subquery Optimizations

Force join order with STRAIGHT_JOIN when the optimizer’s choice is sub‑optimal. Push predicates into derived tables to shrink intermediate result sets. Prefer EXISTS over IN for large sub‑queries because EXISTS stops after the first match. The overarching principle is “filter early, reduce intermediate data”.

8. User‑Defined Variables

Variables such as @rank enable row‑wise calculations before window functions existed. Example: computing daily sales growth by storing the previous day's amount in a variable and using it in the next row’s calculation. Variables are procedural, not standard SQL, and their evaluation order can be unintuitive.

9. Online DDL (Online Schema Change)

MySQL 5.6+ provides ALGORITHM and LOCK clauses for online DDL. ALGORITHM=INPLACE rebuilds indexes without copying the whole table, allowing concurrent DML. LOCK=NONE minimizes blocking. For operations that still require COPY, consider Percona’s pt-online-schema-change tool for truly lock‑free migrations.

10. Generated Columns and Functional Indexes

Generated columns compute values from other columns. A stored generated column can be indexed, enabling fast searches on expressions such as CONCAT(first_name, ' ', last_name). Virtual columns save space but incur CPU cost; stored columns trade space for speed.

These ten techniques form a toolbox for tackling a wide range of performance challenges in MySQL, from basic query inspection to schema evolution on live systems.