How to Diagnose MySQL Slow Queries Without Relying on Blind Indexing

Background and Problem

Slow queries are a common cause of application latency. Instead of immediately asking developers to add an index, a systematic investigation is required: confirm the slow query, examine the execution plan, understand the optimizer's decisions, locate the real bottleneck, and then choose the most appropriate optimization technique (indexing, SQL rewrite, configuration changes, sharding, caching, etc.). The methods shown use MySQL as an example but apply to PostgreSQL, Oracle, and other major databases.

1. Discovering and Confirming Slow Queries

1.1 Enable the Slow Query Log

MySQL disables the slow‑query log by default. Enable it and set a threshold (e.g., 1 second) with the following commands:

-- View current slow‑query variables
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time%';
SHOW VARIABLES LIKE 'log_output%';

-- Enable the log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_output = 'FILE';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Persist the settings in /etc/mysql/my.cnf (or /etc/my.cnf) and restart MySQL:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_output = FILE

systemctl restart mysql
systemctl restart mysqld

1.2 Interpreting the Slow‑Query Log Format

A typical entry looks like:

# Time: 2024-01-15T10:30:45.123456Z
# User@Host: app_user[app_user] @ localhost []
# Query_time: 5.234567  Lock_time: 0.001234  Rows_sent: 100  Rows_examined: 50000
SET timestamp=1705315845;
SELECT * FROM orders WHERE user_id = 12345 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;

Query_time

is the core metric; a high Rows_examined / Rows_sent ratio indicates inefficient scanning.

1.3 Analyzing with mysqldumpslow

Use mysqldumpslow to summarize the log:

# Show the 10 slowest queries
mysqldumpslow -t 10 -s t /var/log/mysql/slow.log

# Show queries with the most rows examined
mysqldumpslow -t 10 -s r /var/log/mysql/slow.log

# Filter by pattern
mysqldumpslow -t 10 -g 'orders' /var/log/mysql/slow.log

1.4 Deep Analysis with pt‑query‑digest

Percona Toolkit provides richer insights than mysqldumpslow:

# Install the toolkit
apt-get install percona-toolkit   # Debian/Ubuntu
yum install percona-toolkit       # RHEL/CentOS

# Basic usage
pt-query-digest /var/log/mysql/slow.log

# Filter by time range
pt-query-digest --since '24h' /var/log/mysql/slow.log
pt-query-digest --since '2024-01-15 10:00:00' --until '2024-01-15 12:00:00' /var/log/mysql/slow.log

The output includes query‑time distribution, execution‑plan summary, and flags that may indicate problems.

2. Using EXPLAIN to Analyze Execution Plans

2.1 Basic EXPLAIN Usage

EXPLAIN SELECT * FROM orders WHERE user_id = 12345;
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 12345;

2.2 Detailed Output Fields

The columns id, select_type, table, type, key, rows, filtered, and Extra each convey specific optimization information. For example, type values from best to worst are: system, const, eq_ref, ref, range, index, ALL. A type of ALL means a full table scan and is a prime candidate for indexing.

2.3 Interpreting Extra

Common values include: Using filesort – requires an extra sort, often solvable by an appropriate index. Using temporary – creates a temporary table, usually due to GROUP BY or ORDER BY without supporting index. Using index condition – index‑condition pushdown is active. Using where – filtering occurs after row retrieval.

2.4 EXPLAIN ANALYZE (MySQL 8.0+)

MySQL 8.0 adds EXPLAIN ANALYZE, which executes the query and reports actual runtime statistics, allowing comparison between estimated and real rows.

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 12345 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;

3. Index Creation and Optimization

3.1 Index Fundamentals

MySQL primarily uses B‑Tree indexes. They provide fast point lookups and range scans but add storage overhead and write‑costs.

3.2 Principles for Creating Indexes

Choose columns with high selectivity (distinct/total ratio). Example to measure selectivity:

SELECT COUNT(DISTINCT status) / COUNT(*) FROM orders;   -- low selectivity
SELECT COUNT(DISTINCT user_id) / COUNT(*) FROM orders; -- high selectivity

Follow the left‑most prefix rule for composite indexes: INDEX idx(a,b,c) can serve queries filtering on a, a,b, or a,b,c, but not on b alone.

CREATE INDEX idx_orders_user_status ON orders(user_id, status, created_at);

3.3 Practical Index Creation

Based on a slow query such as:

SELECT * FROM orders WHERE user_id = 12345 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;

Create a composite index that matches the WHERE clause and the ORDER BY column (descending if MySQL 8.0+):

CREATE INDEX idx_orders_user_status_created ON orders(user_id, status, created_at DESC);

For MySQL 5.7, split into two indexes:

CREATE INDEX idx_orders_user_status ON orders(user_id, status);
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

3.4 Index Usage Tips

Avoid functions on indexed columns (e.g., YEAR(created_at)) and leading wildcards in LIKE patterns, both of which invalidate the index.

4. SQL Statement Optimizations

4.1 Common Inefficient Patterns

Never use SELECT * when only a few columns are needed; always apply LIMIT for pagination.

-- Inefficient
SELECT * FROM orders WHERE order_id = 12345;

-- Efficient
SELECT order_id, user_id, status, total_amount, created_at FROM orders WHERE order_id = 12345;

4.2 Optimizing ORDER BY and GROUP BY

Create indexes that cover the ordering or grouping columns to avoid filesort:

CREATE INDEX idx_orders_user_status_created ON orders(user_id, status, created_at DESC);
SELECT * FROM orders WHERE user_id = 123 ORDER BY status, created_at DESC LIMIT 20;

4.3 Optimizing Joins

Ensure join columns are indexed and prefer driving the join with the smaller table:

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_users_id ON users(id);
EXPLAIN SELECT o.*, u.name FROM orders o JOIN users u ON o.user_id = u.id WHERE o.user_id = 12345;

4.4 Using EXISTS Instead of IN

-- Less efficient
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 'vip');

-- More efficient
SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.status = 'vip');

5. Database Configuration Tuning

5.1 Key Configuration Parameters

[mysqld]
innodb_buffer_pool_size = 12G          # ~70‑80% of RAM
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1
max_connections = 500
query_cache_size = 0                 # removed in MySQL 8.0
tmp_table_size = 256M
max_heap_table_size = 256M
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

5.2 InnoDB Buffer Pool Optimization

Monitor usage with:

SHOW STATUS LIKE 'Innodb_buffer_pool%';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SET GLOBAL innodb_buffer_pool_size = 12873741824;  # 12 GB
innodb_buffer_pool_instances = 8;
innodb_buffer_pool_load_at_startup = 1;

5.3 Connection Management

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';
SET GLOBAL max_connections = 1000;
-- Kill idle connections > 1 hour
SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist WHERE Command='Sleep' AND Time>3600;

6. Real‑World Slow‑Query Optimization Cases

6.1 Pagination Optimization

Deep pagination with large offsets is costly. Replace offset‑based pagination with keyset pagination:

-- Keyset pagination using the last seen order_id
SELECT * FROM orders WHERE order_id < 1234567 ORDER BY order_id DESC LIMIT 20;

6.2 Aggregation Optimization

Instead of scanning the whole table each time, maintain a daily summary table and query it:

CREATE TABLE orders_daily_summary (
  stat_date DATE PRIMARY KEY,
  order_count INT NOT NULL DEFAULT 0,
  total_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO orders_daily_summary (stat_date, order_count, total_amount)
SELECT DATE(created_at), COUNT(*), SUM(total_amount)
FROM orders
WHERE DATE(created_at) = '2024-01-15'
ON DUPLICATE KEY UPDATE
  order_count = VALUES(order_count),
  total_amount = VALUES(total_amount);

SELECT * FROM orders_daily_summary WHERE stat_date >= '2024-01-01';

6.3 Fuzzy Search Optimization

Full‑text indexes or external search engines replace leading‑wildcard LIKE patterns:

-- Full‑text index (MySQL 5.6+)
ALTER TABLE users ADD FULLTEXT INDEX ft_users_name(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('zhang');

-- Prefix index for known prefix length
CREATE INDEX idx_users_name_prefix ON users(name(10));
SELECT * FROM users WHERE name LIKE 'zhang%';

7. Monitoring and Prevention

7.1 Continuous Slow‑Query Monitoring

#!/bin/bash
DATE=$(date -d "yesterday" +%Y-%m-%d)
SLOW_LOG="/var/log/mysql/slow.log"
REPORT="/var/log/mysql/slow_query_report_${DATE}.txt"

pt-query-digest --since "$(date -d 'yesterday 00:00:00' +%s) seconds" \
  --until "$(date -d 'yesterday 23:59:59' +%s) seconds" \
  --report-format=query_report $SLOW_LOG > $REPORT

if [ -s "$REPORT" ]; then
  count=$(grep -c "Query" $REPORT || true)
  if [ "$count" -gt 10 ]; then
    echo "Found $count slow queries in the report" | mail -s "Slow Query Alert" [email protected]
  fi
fi

7.2 Using Performance Schema

-- Enable statement instrumentation
UPDATE performance_schema.setup_instruments SET ENABLED='YES' WHERE NAME LIKE 'statement/%';
-- Enable consumers
UPDATE performance_schema.setup_consumers SET ENABLED='YES' WHERE NAME LIKE 'events_statements%';

-- Show top 10 slowest statements
SELECT DIGEST,
       COUNT_STAR,
       SUM_TIMER_WAIT/1000000000000 AS total_time_sec,
       AVG_TIMER_WAIT/1000000000000 AS avg_time_sec,
       SUM_ROWS_EXAMINED,
       SUM_ROWS_SENT,
       SUBSTR(DIGEST_TEXT,1,100) AS query_sample
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

7.3 Slow‑Query Governance Process

Daily monitoring of newly logged slow queries.

Root‑cause analysis with EXPLAIN.

Design optimization plan (index, rewrite, config).

Validate improvements in a staging environment.

Archive the issue and solution to a knowledge base.

8. Conclusion

Optimizing slow queries requires a systematic workflow: enable and collect the slow‑query log, analyze it with tools like mysqldumpslow or pt‑query‑digest, inspect execution plans via EXPLAIN, apply the most suitable fixes (indexes, SQL rewrites, configuration tweaks), and maintain continuous monitoring to avoid regressions. Blindly adding indexes without analysis can worsen performance; a disciplined, data‑driven approach yields reliable improvements.

Key references include the MySQL 8.0 Reference Manual (optimizing queries and EXPLAIN), Percona’s pt‑query‑digest documentation, and the book *High Performance MySQL*.