MySQL Slow Query Log: Find and Fix Queries Slowing Your Site

Your site loads slowly, but top or htop shows CPU and RAM sitting comfortably under load. That's usually a sign the bottleneck isn't the server — it's the database. A handful of badly-written or unindexed queries can quietly add seconds to every page load without ever pushing your CPU graphs into the red. The slow query log is the tool that tells you exactly which queries are the problem, instead of you guessing.

Symptom: Pages Are Slow, But the Server "Looks" Fine

This is the pattern that sends people looking in the wrong place: PHP-FPM workers aren't maxed out, memory isn't exhausted, load average is reasonable — yet a product page or search results page takes 3-6 seconds to render. If you check MySQL's process list mid-request, you'll often see one or two queries sitting in Sending data or Copying to tmp table state for way longer than they should.

Run this while the site feels slow to catch it in the act:

mysqladmin -u root -p processlist

or, if you have SHOW PROCESSLIST access via phpMyAdmin, watch it under Status → Processes. But catching a slow query live is luck. The slow query log catches it every single time it happens, over hours or days, without you babysitting a terminal.

Cause: Missing Indexes, N+1 Queries, and Unbounded JOINs

In practice, almost every "slow query" traces back to one of these:

• Missing or unused indexes — a WHERE clause filtering on a column with no index forces MySQL to scan every row in the table.
• N+1 query patterns — a plugin or theme running one query per item in a loop instead of one query for the whole set. Common in poorly coded WooCommerce extensions and custom WordPress loops.
• Unbounded JOINs on large tables — joining wp_postmeta or wp_options without a tight WHERE clause on a site with years of accumulated data.
• Full table scans from LIKE '%term%' — a leading wildcard can't use a normal index at all.
• ORDER BY on a non-indexed column combined with a large result set, forcing MySQL into a filesort.

None of these show up as "high CPU" in a simple sense — they show up as MySQL threads waiting, which is exactly what the slow query log records.

Fix: Enable, Read, and Act on the Slow Query Log

Step 1 — Enable the Slow Query Log

On a VPS with root access, edit your MySQL/MariaDB config, usually /etc/mysql/mariadb.conf.d/50-server.cnf or /etc/my.cnf:

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

long_query_time = 1 logs anything taking over 1 second. Start there — on a busy production site, setting it to 0.1 seconds can flood the log and add its own overhead. Restart MySQL to apply:

systemctl restart mysql

Prefer not to touch a config file? You can toggle it live for the current session without a restart:

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

This resets on the next MySQL restart, so it's useful for a quick diagnostic pass but not for permanent monitoring.

On cPanel/WHM shared or managed environments, you generally won't have write access to my.cnf for the shared MySQL instance — on Getwebup shared hosting, our support team can enable slow query logging for your database on request, or pull a snapshot of current slow queries for you directly.

Step 2 — Let It Collect, Then Read It

Give it a few hours of real traffic, then summarize the log instead of reading raw entries one by one:

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

-s t sorts by total time consumed, -t 10 shows the top 10 offenders. This is the important bit: sort by total time, not by single-query duration. A query that takes 0.3 seconds but runs 5,000 times a day usually costs you more than one that takes 4 seconds and runs twice.

Prefer more detail? pt-query-digest from Percona Toolkit gives a much richer breakdown (query fingerprints, percentiles, example EXPLAIN targets) if it's installed:

pt-query-digest /var/log/mysql/mysql-slow.log

Step 3 — EXPLAIN the Worst Offenders

Take the top query from the summary and run it through EXPLAIN:

EXPLAIN SELECT * FROM wp_postmeta WHERE meta_key = 'some_key' AND meta_value = 'x';

Look at the type and rows columns. type: ALL means a full table scan — that's your smoking gun. A high number in rows relative to your actual table size confirms it. The fix is usually one of:

• Add an index on the filtered/joined column: ALTER TABLE wp_postmeta ADD INDEX idx_meta_key (meta_key);
• Rewrite the query to avoid a leading wildcard LIKE, or move that search to a dedicated search plugin/service.
• Cap unbounded result sets with a LIMIT and paginate instead of pulling everything at once.
• If it's coming from a plugin, check for a newer version — N+1 query bugs get fixed in changelogs more often than you'd expect.

Be deliberate with new indexes on a live table — each one speeds up reads but adds overhead to every INSERT/UPDATE, and on a multi-million-row table an ALTER TABLE can lock things up. Test on a staging copy first, and run it during low-traffic hours if the table is large.

Step 4 — Turn It Off When You're Done

The slow query log itself has a small write overhead. Once you've diagnosed and fixed the offending queries, either disable it or bump long_query_time back up:

SET GLOBAL slow_query_log = 'OFF';

Or leave it running permanently at a higher threshold (2-3 seconds) as an early warning system — just make sure something rotates the log file so it doesn't quietly fill your disk.

Prevention: Catch This Before It's a Fire

• Log-rotate the slow log if you keep it running long-term — add it to /etc/logrotate.d/mysql-server so it doesn't grow unbounded.
• Review new plugins before installing them on WordPress/WooCommerce sites — a query profiler plugin like Query Monitor will flag N+1 patterns in staging before they hit production.
• Index your wp_postmeta/custom table hot columns proactively if you know a plugin filters on custom meta keys heavily.
• Re-run this audit after major traffic growth — a query that was fine at 500 rows can become the site's biggest bottleneck at 500,000 rows.
• Pair this with server-level monitoring — if you also see high CPU alongside slow queries, check PHP-FPM pool sizing and MySQL's Threads_connected too, since an overloaded query layer often drags the whole stack down with it.

If you're on a Getwebup VPS or managed hosting plan and want a second pair of eyes on a slow query log, our support team can help you read the output and prioritize fixes — sometimes it's one missing index away from a completely different site.