Master MySQL Performance: From Tuning Pyramid to Explain Plans

1. MySQL Tuning Pyramid

The higher you climb the pyramid, the harder the optimization and the smaller the gains. Start with architecture: consider moving non‑relational workloads to data warehouses, search engines, or caches; then evaluate write concurrency and distribution; finally assess read pressure and read/write splitting. For critical or financial applications, also factor in data‑safety requirements. Only after the architecture is sound does DBA‑level tuning become effective.

At the MySQL level, verify that table schemas are sound, SQL statements are optimized, necessary indexes exist, and redundant indexes are removed.

Hardware and OS tuning matters too—SSD over HDD, appropriate RAID level, disk‑IO distribution, raw devices, XFS file system, and OS scheduler settings can all impact performance.

2. Query Performance Optimization

Even with perfect schema and indexes, a poorly written query will cripple performance.

What is a slow query?

A slow‑query log records any statement that exceeds the long_query_time threshold (default 10 seconds). The log is disabled by default and must be enabled explicitly.

Basic steps to reduce data access

Confirm the application isn’t retrieving more rows or columns than needed.

Check whether MySQL is scanning more rows than necessary at the server layer.

Common pitfalls

Fetching unnecessary rows: MySQL returns the full result set before the client discards excess rows.

Selecting all columns (SELECT *): Prevents index‑only (covering) scans and forces extra I/O.

Repeated identical queries: Cache results instead of re‑executing.

3. Slow‑Query Configuration

show VARIABLES like 'slow_query_log';

Enable the log: set GLOBAL slow_query_log=1; Set the threshold (default 10 seconds):

show VARIABLES like '%long_query_time%';

set global long_query_time=0;  -- for demo purposes

Log queries that don’t use indexes:

show VARIABLES like '%log_queries_not_using_indexes%';

4. EXPLAIN Execution Plan

Running EXPLAIN SELECT … shows how MySQL will execute a query, including table read order, access type, possible and actual indexes, estimated rows, and extra information.

Key columns

id: Unique identifier for each SELECT in the statement (including subqueries and UNIONs).

type: Access method, ordered from best to worst: system > const > eq_ref > ref > range > index > ALL.

possible_keys / key: Indexes that could be used vs. the one actually chosen.

key_len: Length of the index used (helps see how many columns of a composite index are applied).

rows: Estimated rows examined.

filtered: Estimated percentage of rows that pass remaining conditions.

Extra: Additional details such as “Using index” (covering index) or “Using where”.

Examples illustrate how subqueries, UNION, and UNION ALL affect the plan, how the optimizer may rewrite subqueries into joins, and how temporary tables appear for UNION operations.

5. Query Optimizer Overview

The optimizer parses the query, builds an internal representation, and then chooses the best execution plan based on cost estimates, storage‑engine information, and available indexes.

6. High‑Performance Index Usage Strategies

1) Avoid operations on indexed columns

Expressions like id + 1 = 17 or functions on indexed columns prevent index usage. Keep the indexed column alone on one side of the comparison.

2) Full‑value matches

When a composite index covers columns (insert_time, order_status, expire_time), a query that specifies all three can use the index fully.

3) Left‑most prefix rule

Only the leftmost contiguous columns of a composite index can be used. Skipping a column disables the later columns.

4) Put range conditions last

Range scans on the leftmost column allow subsequent columns to be used; mixing exact matches with ranges follows the same rule.

5) Prefer covering indexes

If the query can be satisfied entirely from the index (no table lookup), performance improves dramatically because index entries are much smaller than full rows.

6) Avoid NOT (=, <>) when possible

Negation often forces a full table scan.

7) Be careful with NULL checks

Indexes on nullable columns may behave differently for IS NULL vs. IS NOT NULL. Designing columns as NOT NULL when possible avoids extra overhead.

8) LIKE patterns

Leading wildcards ( '%abc') disable index usage; consider full‑text indexes or restructure the query.

9) Use proper quoting for string literals

Unquoted strings can cause implicit type conversion and index loss.

10) OR conditions

OR on the same indexed column can still use the index; OR across different columns usually forces a full scan unless both columns are indexed.

11) Index‑based sorting and grouping

If the ORDER BY clause matches the index order (including direction), MySQL can avoid an extra sort step.

12) Consistent ASC/DESC ordering

Mixed ASC/DESC in ORDER BY prevents index‑based sorting.

13) Insert rows in primary‑key order

Random primary‑key inserts (e.g., UUID) cause page splits and fragmented I/O. AUTO_INCREMENT or monotonic keys are preferred.

14) Optimizing COUNT()

COUNT(*) scans many rows; using an index‑only count or maintaining a summary table can reduce cost.

15) Optimizing LIMIT pagination

Large offsets cause MySQL to read and discard many rows. Use “keyset pagination” (e.g., WHERE id > last_id ORDER BY id LIMIT N) to avoid the penalty.

16) NULL handling in statistics

The innodb_stats_method variable controls how NULLs are treated during index statistics collection (nulls_equal, nulls_unequal, nulls_ignored). Modern MySQL versions treat NULLs as equal, so avoid nullable columns when possible.

Key Takeaways

Effective MySQL performance tuning starts with a solid architecture, proceeds through careful schema and index design, and finishes with query‑level refinements such as using covering indexes, avoiding unnecessary columns, and leveraging EXPLAIN to verify execution plans. Understanding how the optimizer evaluates cost, how different access types behave, and how to write pagination‑friendly queries can dramatically improve throughput and latency.