MySQL Slow Query Triage for Hosting Engineers

On a managed hosting server you almost never own the application. You can't read its source, you can't refactor its ORM, and the developer who wrote the query left two years ago. What you can do is find the specific query that's hurting the server, prove it, and either fix it at the database layer or hand the developer something actionable.

This is that workflow.

Step 1: confirm it's actually the database

Before blaming MySQL, check that MySQL is the bottleneck rather than a victim of one. A server that's swapping makes every query slow — that's a memory problem wearing a database costume.

# What's running right now
mysqladmin processlist | head -30

# Queries running longer than 5 seconds — the ones that matter
mysql -e "SELECT id, user, db, time, state, LEFT(info, 120) AS query
          FROM information_schema.processlist
          WHERE command != 'Sleep' AND time > 5
          ORDER BY time DESC;"

A processlist full of Sending data or Copying to tmp table entries with high time values is a genuine query problem. A processlist that's mostly idle while load is high means look elsewhere.

Step 2: turn on the slow query log

The processlist is a snapshot. The slow query log is the record — and it's the single most useful diagnostic MySQL offers.

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

Step 3: analyse it properly

Don't read the raw log. Aggregate it — you want the query that costs the most in total, which is often not the slowest single query.

# Bundled with MySQL — sort by total time, top 10
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# Percona Toolkit — better output, worth installing
pt-query-digest /var/log/mysql/slow.log | head -60

pt-query-digest gives you a ranked profile that looks roughly like this:

# Profile
# Rank Query ID           Response time   Calls R/Call
# ==== ================== =============== ===== ======
#    1 0x3A2E9F1C...       842.1131 61.2%  4021 0.2094
#    2 0x9B4D2E88...       201.7742 14.7%     3 67.2581
#    3 0x1F7C0A55...        98.4410  7.1%  8802 0.0112

Read that carefully. Query #2 is the slowest per call at 67 seconds — genuinely alarming. But query #1 consumes 61% of all database time because it runs four thousand times. Fix #1 first. This is exactly the judgement the aggregate view gives you and a raw log doesn't.

Step 4: EXPLAIN the offender

EXPLAIN SELECT * FROM wp_postmeta
WHERE meta_key = '_order_total' AND meta_value > 100;

id  select_type  table        type  possible_keys  key   rows      Extra
1   SIMPLE       wp_postmeta  ALL   NULL           NULL  2841003   Using where

Three fields tell the story:

• type: ALL — a full table scan. Every row read to answer the query.
• key: NULL — no index used at all.
• rows: 2841003 — the estimated number of rows examined. Compare that with how many the query actually returns; a huge gap is the problem in one number.

The type column, best to worst:

And in Extra, three phrases that mean real work is happening on disk:

• Using filesort — results sorted outside an index. Expensive on large sets.
• Using temporary — a temp table was built; if it exceeds tmp_table_size it lands on disk.
• Using join buffer — a join running without a usable index on the joined column.

Step 5: add the index — carefully

-- What already exists?
SHOW INDEX FROM wp_postmeta;

-- Table size, so you know what the operation will cost
SELECT table_name,
       ROUND(data_length/1024/1024) AS data_mb,
       ROUND(index_length/1024/1024) AS index_mb,
       table_rows
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY data_length DESC LIMIT 10;

-- Create it (online DDL avoids locking on modern InnoDB)
ALTER TABLE wp_postmeta
  ADD INDEX idx_meta_key_value (meta_key, meta_value(20)),
  ALGORITHM=INPLACE, LOCK=NONE;

Index principles worth holding on to:

• Order matters in composite indexes. An index on (a, b) serves queries filtering on a, or on a AND b — but not b alone.
• Most selective column first, generally — the one that eliminates the most rows.
• Indexes cost writes. Every INSERT and UPDATE maintains them. Don't index everything.
• Prefix-index long text columns (meta_value(20)) rather than indexing the whole column.

Step 6: check the obvious config mistakes

Before deep tuning, verify the two settings that are wrong most often:

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

-- Buffer pool efficiency: reads served from memory vs. from disk
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

innodb_buffer_pool_size is the single most impactful setting. On a dedicated database server, 60–70% of RAM. On a shared hosting box also running Apache and PHP, much less — but the default of 128 MB is almost always far too small, and I've found it unchanged on servers with 64 GB of RAM.

Compare Innodb_buffer_pool_reads (served from disk) against Innodb_buffer_pool_read_requests (served from memory). If the disk figure is more than a percent or so of the total, your working set doesn't fit in the pool.

max_connections set very high is a common cargo-cult fix. It doesn't add capacity — it just lets more clients queue up simultaneously, converting a fast failure into a slow, memory-hungry one. If you're hitting the limit, the fix is upstream: fewer PHP-FPM children, or connection pooling.

Step 7: when you can't fix it yourself

Sometimes the query is genuinely bad and no index saves it — a SELECT * with three joins and no LIMIT against a table that grows forever. That needs an application change, and your job becomes making the case unarguable.

Give the developer:

• The exact query text, normalised, from the digest.
• Its EXPLAIN output showing the scan.
• Call count and total database time — "this is 61% of all query time on the server".
• Rows examined versus rows returned, which usually makes the problem self-evident.
• A specific suggestion: the index you'd add, or the missing LIMIT.

That's a great deal more persuasive than "the database is slow", and in my experience it's the difference between a fix this sprint and a ticket that ages for six months.

The method in one line: confirm it's the database, log the slow queries, aggregate by total time not per-call time, EXPLAIN the top offender, and fix what that reveals. Most of the time it's a missing index and you can fix it yourself in a window. The rest of the time, you now have the evidence to get someone else to.