Master MySQL Slow Query Analysis: Indexes, EXPLAIN, and Optimization Tips

MySQL slow query analysis focuses on three key metrics: response time (service time + queue time), rows scanned, and rows returned.

Response time includes the time the server spends processing the query and the time waiting for resources such as I/O or row locks.

Rows scanned indicate how much data a query accesses; reducing scanned rows or columns (avoiding SELECT *) often yields performance gains.

Rows returned add extra I/O, memory and CPU cost; using LIMIT can reduce this.

Analysis Example

In the Sakila database, the query select * from film_actor where film_id = 1 uses the indexed column film_id with a ref access type, scanning only 10 rows. The EXPLAIN output is shown below:

Removing the index forces a full table scan ( ALL) and MySQL estimates scanning 5,462 rows. The resulting EXPLAIN output is:

Using WHERE with Indexes

Filter rows directly in the index.

Use a covering index (shown as Using index) to return rows without accessing the table.

Read rows from the table then filter with Using where.

Three‑Star Index Evaluation

One star: index groups related records together.

Two stars: index column order matches query order.

Three stars: index contains all columns required by the query.

Clustered Index

InnoDB stores the primary key and row data together in a clustered B‑tree; a table can have only one clustered index. It speeds data access but makes insert speed dependent on insert order and makes updates costly.

Covering Index

A covering index contains all columns needed by a query, allowing MySQL to satisfy the query using only the index, which reduces I/O and benefits InnoDB because secondary index leaf nodes store the primary key.

Index Column Order

When sorting and grouping are not required, place the most selective column first. Example: for

select * from payment where staff_id = 123 and customer_id = 456

put customer_id first if it has higher selectivity.

Left‑most Prefix Rule

MySQL can use an index only if the query’s WHERE conditions start from the leftmost index column without skipping columns.

Sorting and Filesort

If the ORDER BY column order matches the index order and direction, MySQL can avoid a filesort. Constant leading columns can also satisfy the rule. Otherwise, MySQL performs a filesort, which is costly.

Examples that avoid filesort:

select * from rental where rental_date = '2005-05-25' order by inventory_id, customer_id

Examples that require filesort or violate the left‑most prefix rule are also shown, illustrating how range conditions, mismatched column order, or missing index columns prevent index‑based sorting.