Query Performance Basics

Introduction

Slow queries frustrate users and overload servers. This chapter reads EXPLAIN in depth, enables the slow query log, applies optimization habits, and outlines server knobs like max_connections and InnoDB Buffer Pool—enough to diagnose common dev and small production issues.

Prerequisites

EXPLAIN Columns (Traditional)

sql

EXPLAIN
SELECT p.title, u.display_name
FROM posts p
INNER JOIN users u ON u.id = p.user_id
WHERE p.published_at >= '2026-01-01';

Column	Watch for
id	Query block id
select_type	SIMPLE, SUBQUERY, DERIVED, etc.
table	Table or alias
type	`ALL` worst; `index`, `range`, `ref`, `const` better
possible_keys	Candidates
key	Index chosen—NULL often bad on big tables
rows	Estimated rows read
Extra	`Using filesort`, `Using temporary`, `Using index`

Using index — covering index good.

Using filesort / Using temporary — may need index or query rewrite.

MySQL 8:

sql

EXPLAIN ANALYZE SELECT ...;

Shows actual timings—run on dev.

Slow Query Log

Enable session or in my.cnf:

sql

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;

Find log path:

sql

SHOW VARIABLES LIKE 'slow_query_log_file';

Analyze with mysqldumpslow (if installed):

bash

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

pt-query-digest (Percona Toolkit)—conceptual richer reports.

Optimization Checklist

Habit	Why
Avoid *`SELECT `**	Less I/O
Index WHERE and JOIN columns	Cut rows examined
Match composite index left prefix	See indexes chapter
Paginate with stable ORDER BY + keyset for huge tables	Offset slow at depth
Replace correlated subquery with join	Often faster
Batch writes in transactions	Fewer fsync rounds

Bad pattern:

sql

SELECT * FROM posts WHERE YEAR(published_at) = 2026;

Better range on indexed column:

sql

SELECT id, title FROM posts
WHERE published_at >= '2026-01-01' AND published_at < '2027-01-01';

Server Tuning (Concept)

Variable	Role
`max_connections`	Cap concurrent clients—raise with RAM if `Too many connections`
`innodb_buffer_pool_size`	Cache for InnoDB data/index pages—often 50–70% RAM on dedicated DB server
`tmp_table_size` / `max_heap_table_size`	Memory for internal temp tables

Check:

sql

SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Query cache removed in MySQL 8.0—do not search obsolete query_cache_* tuning.

Bottleneck Workflow

text

1. User reports slow page
2. Find SQL in app logs
3. EXPLAIN on staging copy
4. Add/fix index or rewrite query
5. Re-measure; check slow log gone

Partition and Replication (Awareness)

Partitioning splits one table into physical parts by RANGE/LIST/HASH—helps prune very large time-series tables; adds operational complexity.

Replication — primary writes, replicas read copies—enables read scaling and failover concepts; not needed on single dev instance.

Deep ops topics—read MySQL docs when data outgrows one server.

FAQ

type=ALL always wrong?

Small tables full scan OK—optimizer chooses cheap path.

Index did not help?

Wrong columns order, stale statistics—ANALYZE TABLE posts;

Buffer pool on laptop?

Default modest—raise slightly if large local dataset testing.

OR kills index?

WHERE a=1 OR b=2 hard—UNION two indexed queries sometimes better.

COUNT(*) slow?

InnoDB approximate count expensive—cache counts or summary table.

Profiling?

EXPLAIN ANALYZE preferred over legacy profiling.