Master MySQL Slow Query Optimization: EXPLAIN and Index Strategies

This article introduces methods for optimizing slow SQL queries, beginning with an overview of the EXPLAIN statement, which is essential for analyzing query execution plans.

1. EXPLAIN Detailed Explanation

EXPLAIN provides a row for each SELECT in a query, showing how the optimizer will execute the statement. The most important columns are described below:

id : Unique identifier for each SELECT in the query.

select_type : Type of SELECT (e.g., SIMPLE, PRIMARY, UNION, SUBQUERY, DERIVED).

table : Table name referenced.

partitions : Partition information matched.

type : Access method for the table (e.g., const, ref, range, index, all, eq_ref).

possible_keys : Indexes that could be used.

key : Index actually used.

key_len : Length of the used index.

ref : Columns or constants compared to the index.

rows : Estimated number of rows examined.

filtered : Percentage of rows filtered after applying the WHERE clause.

Extra : Additional information about the execution plan.

1.1 select_type

SIMPLE: No UNION or subqueries.

PRIMARY: Leftmost SELECT in a UNION or subquery.

UNION: Subsequent SELECTs in a UNION.

UNION RESULT: Temporary table used for UNION deduplication.

DEPENDENT UNION, SUBQUERY, DEPENDENT SUBQUERY, MATERIALIZED, DERIVED: Various subquery and derived table types.

1.2 type

const: Access by primary key or unique index with constant comparison.

ref: Single‑point scan using a non‑unique index.

ref_or_null: Like ref but also scans NULL values.

range: Scans a range of index values.

index: Full index scan (covering index).

fulltext: Full‑text index access.

all: Full table scan.

eq_ref: Join using primary key or unique non‑NULL index.

1.3 ref

When the access type is const, ref, ref_or_null, eq_ref, unique_subquery, or index_subquery, the ref column shows what is compared to the index:

const: A constant value.

func: A function.

db.table.column: A column from a specific table.

1.4 Extra

No Table used: Query without a FROM clause.

Impossible WHERE: WHERE condition always false.

No matching min/max row: MIN or MAX returns no rows.

Using Index: Covering index used.

Using Index condition: Index condition pushdown applied.

Using join buffer (Block Nested Loop): Join performed with a join buffer.

Using intersect(...), Using union(...), Using sort union(...): Index merge strategies.

Using filesort: Sorting performed without index.

Using temporary: Internal temporary table created.

Index condition pushdown evaluates all conditions on the secondary index before fetching rows, reducing unnecessary row lookups.

2. Optimization Considerations

Based on Access Type

When the observed type is not optimal, adding appropriate indexes to the search columns is the simplest remedy.

Reducing Scanned Rows

For queries that aggregate large numbers of rows but return few results, consider creating a summary table or rewriting the query.

Complex Query vs. Multiple Simple Queries

Splitting a complex query into several simple ones can reduce the workload on MySQL and shift processing to the application layer.

Chunked Processing

When handling massive data sets, process them in chunks (e.g., 10,000 rows at a time) and pause briefly between chunks to avoid long locks and high resource consumption.

Join Optimization

Ensure columns used in ON or USING clauses are indexed.

Make sure GROUP BY and ORDER BY reference only columns from a single table to allow index usage.

IN() vs. OR

MySQL sorts the IN() list and performs a binary search (O(log n)), which is faster than a series of OR conditions (O(n)) for large lists.

When Indexes Fail

Skipping the leftmost column of a composite index disables its use.

Range conditions on a column prevent the use of indexes on columns to its right.

Functions or expressions on indexed columns disable index usage.

Leading wildcard (e.g., %abc) in a LIKE predicate forces a full table scan.

ORDER BY Index Issues

Column order must match the index order.

Mixed ASC/DESC prevents index usage (except in MySQL 8.0 where mixed order is supported).

Columns from different indexes cannot be used together for ordering.

Non‑contiguous columns of a composite index break index ordering.

Functions on ORDER BY columns disable index usage.

Non‑Null Index Columns

When MIN() or MAX() operate on a NOT NULL indexed column, the optimizer can retrieve the result directly from the index edge.

Duplicate and Redundant Indexes

Duplicate indexes on the same columns should be removed. Redundant indexes arise when a new composite index subsumes an existing single‑column index; usually the latter can be dropped.

Index Merge Strategies

MySQL can combine multiple single‑column indexes using three strategies:

Intersection : Intersect ordered primary key lists from each index.

Union : Union ordered primary key lists (requires ordered results).

Sort‑Union : Union followed by sorting when the lists are unordered.

If index merge is costly, consider rebuilding indexes, using UNION ALL, or disabling index_merge via SET optimizer_switch='index_merge=off'. The IGNORE INDEX hint can also force the optimizer to skip specific indexes.

Optimizing COUNT()

Use COUNT(col) or COUNT(0) to count non‑NULL rows, and COUNT(*) for total rows. For large tables, approximate row counts using the rows estimate from EXPLAIN, remove unnecessary DISTINCT, or simplify WHERE clauses.

Optimizing UNION Queries

Prefer UNION ALL when duplicate elimination is not required, and apply WHERE, LIMIT, and ORDER BY to each subquery for better optimization.

Optimizing OFFSET

Instead of using large OFFSET values, implement keyset pagination (bookmarking) by remembering the last retrieved ID and querying with WHERE id > last_id LIMIT 20. This avoids scanning and discarding rows.

Using WITH ROLLUP for GROUP BY

While WITH ROLLUP can compute subtotals, moving that logic to the application layer often yields better performance.

OPTIMIZE TABLE and Table Maintenance

After massive deletions or non‑sequential inserts, run OPTIMIZE TABLE (or ALTER TABLE ... ENGINE=InnoDB) to reclaim space and defragment data. Verify success by checking data_free in SHOW TABLE STATUS.

Detecting and Repairing Corrupted Tables

Use CHECK TABLE to detect errors and REPAIR TABLE or rebuild the table with ALTER TABLE ... ENGINE=InnoDB to fix them.

References: High Performance MySQL (4th edition), MySQL Internals, optimizer_switch documentation, index hints, and related MySQL resources.