How do I identify slow queries in MySQL?

Enable the slow query log (`slow_query_log = 1`) and review slow queries using `explain` statements to analyze query executions.

Database Performance Playbook

Problem Statement

High database latency directly impacts application response times. When user concurrency scales, unoptimized query paths, missing indexes, and lock contention lead to connection pool exhaustion and database locks. This framework provides a systematic diagnostic sequence to identify, score, and resolve database performance bottlenecks.

When to Use

Use this framework when database CPU utilization exceeds 70%, when query response times exceed the 200ms latency budget, or during quarterly capacity planning audits.

Step-by-Step Diagnostic Sequence

Step 1: Bottleneck Isolation

Run the following queries to isolate connection count saturation and identify lock-waiting queries:

-- MySQL: Identify active transactions waiting for locks
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

Step 2: Query Plan Evaluation

Prefix slow queries with EXPLAIN FORMAT=JSON or EXPLAIN ANALYZE to inspect:

Query Type: Look for type: ALL (full table scan) or type: index (full index scan). Both indicate index coverage failures.
Filter Efficiency: Compare rows (estimated rows evaluated) against filtered (percentage of rows matching conditions). A low filtered percentage indicates inefficient index boundaries.
Key Length: Validate key_len to ensure composite indexes are fully utilized, not partially matched.

Step 3: Composite Index Selection Matrix

Use this decision model to construct multi-column indexes:

Query Condition Pattern	Optimal Index Design	Rationale
`WHERE A = x AND B = y`	Composite Index `(A, B)` or `(B, A)`	Order determined by column cardinality (highest uniqueness first).
`WHERE A = x ORDER BY B`	Composite Index `(A, B)`	Prevents `filesort` operations by utilizing index sorting.
`WHERE A = x AND B > y`	Composite Index `(A, B)`	Place inequality/range columns last to ensure the index search isn't terminated prematurely.

Lock Contention Mitigation Guidelines

Shorten Transactions: Do not make API calls, dispatch emails, or execute complex file uploads inside transaction blocks. Acquire locks at the latest possible execution step.
Enforce Isolation Levels: Use READ COMMITTED instead of REPEATABLE READ in MySQL where acceptable to reduce gap locks.
Avoid Loop Queries (N+1): Preload relationships using eager-loading or join queries rather than firing queries inside loop scopes.

Evaluation Scorecard

Evaluate your database architecture using this performance scoring framework:

Grade A (Optimal): Zero full table scans on tables over 10,000 rows; Slow query ratio < 0.5% of total traffic; Connection pool saturation < 30%.
Grade B (Acceptable): Table scans only on static lookup tables; Slow query ratio < 2% of total traffic; Connection pool saturation < 60%.
Grade C (High Risk): Regular full table scans on transaction tables; Slow query ratio > 5% of total traffic; Lock wait times exceed 2.0s under concurrency. Immediate indexing and query refactoring required.