MySQL Running Slow? Find & Fix Queries With the Slow Log

Your site's server load looks fine, PHP isn't the bottleneck, but pages still take three or four seconds to render. Nine times out of ten when CPU and RAM both look healthy, the actual drag is a handful of MySQL queries doing far more work than they should. Here's how to find them and fix them, on wordpress-missing-a-temporary-folder-upload-error" class="auto-link">cPanel shared hosting or a VPS.

Symptom: Everything Looks Fine Except the Database

This one's sneaky because it doesn't announce itself the way a full disk or an OOM crash does. What you'll actually notice:

• Pages that hit the database — product listings, search results, admin dashboards — are slow, but static pages load instantly.
• top shows mysqld sitting at high CPU for stretches, then dropping back down, in a pattern that tracks with traffic.
• WooCommerce or a large WordPress site gets noticeably slower as the catalog or order history grows, even though nothing else changed.
• phpMyAdmin queries that used to return instantly now spin for several seconds.

If you've already ruled out CPU contention from other processes and nginx-primary-script-unknown-file-not-found-on-a-vps" class="auto-link">PHP-FPM isn't maxed out, the database is the next place to look — not the disk, not the network.

Cause: Queries Running Without Indexes to Use

MySQL has to scan data to answer a query. With a proper index, it can jump straight to the matching rows. Without one, it reads every row in the table — fine at 500 rows, painful at 500,000. The usual culprits:

• A WHERE clause filtering on a column that was never indexed (a common one: post_meta.meta_key in WordPress with a lot of plugins storing custom fields).
• A join between two large tables where the join column isn't indexed on one side.
• An ORDER BY or GROUP BY forcing MySQL to sort the entire result set in memory, or worse, on disk.
• SELECT * pulling large text/blob columns you don't actually need, adding I/O overhead on every row.

None of this shows up in a CPU graph as "bad indexing" — it just shows up as mysqld burning cycles. You need to look inside MySQL itself to see which queries are the problem.

Fix: Turn On the Slow Query Log

The slow query log records every query that takes longer than a threshold you set. It's the single best tool for this because it tells you exactly which query to fix, not just that "the database is slow."

1. Enable It (VPS with root/SSH access)

Edit your MySQL/MariaDB config, usually /etc/mysql/my.cnf or /etc/mysql/mariadb.conf.d/50-server.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 over one second — a reasonable starting point. Drop it to 0.5 on a busy site if you want to catch more borderline cases. Restart the service to apply:

systemctl restart mysql

You can also enable it live without a restart, which is handy for a short diagnostic window instead of leaving verbose logging on permanently:

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

2. Enable It on cPanel Shared Hosting

If you don't have root, you won't be able to edit my.cnf directly. Two options:

• Ask your host to enable the slow query log for your account temporarily — on SkyServer shared plans, support can turn this on for a diagnostic window and hand you the log.
• Use phpMyAdmin > Status > Monitor, which shows currently running queries and can highlight ones that have been executing for a while, without needing log file access.

3. Read the Log With mysqldumpslow

Raw slow query logs are hard to scan by eye once they've collected a few hundred entries. Summarize them:

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

This sorts by total time and shows the 10 worst offenders, with similar queries grouped together (numeric literals and string values get normalized so you're not looking at 500 near-identical rows). Look for the query that appears most often first — a query that runs 2,000 times a day at 300ms adds up to more real-world impact than one slow report that runs once a week.

4. Diagnose With EXPLAIN

Take the worst query from the log and run EXPLAIN in front of it:

EXPLAIN SELECT order_id, customer_email FROM wp_wc_orders
WHERE status = 'processing' AND created_at > '2026-06-01';

The output tells you what MySQL actually did. The columns that matter most:

5. Add the Missing Index

Once you know which column(s) the WHERE/JOIN/ORDER BY depends on, add an index:

ALTER TABLE wp_wc_orders ADD INDEX idx_status_created (status, created_at);

A composite index like this one matches queries filtering on status first and created_at second — order matters, put the column with the most selective filter first. Run EXPLAIN again afterward to confirm type moved from ALL to ref or range and rows dropped sharply.

Don't index everything reflexively, though. Every index speeds up reads but slows down writes (inserts/updates have to update the index too), and adds disk space. Index the columns your slow queries actually filter or join on, not every column in the table.

6. Check for the WordPress-Specific Offenders

If this is a WordPress site, a few patterns show up in the slow log constantly:

• Transients piling up in wp_options — expired transients that were never cleaned up bloat the autoloaded options, and every page load reads them. A plugin like WP-Optimize or a manual DELETE on expired _transient_timeout_ rows fixes this.
• Postmeta queries with no index on meta_key — common with page builders and custom fields plugins that store a lot of metadata per post.
• Revisions bloating wp_posts — thousands of post revisions on a heavily edited site slow down any query that scans the posts table without a tight WHERE.

Prevention: Keep It From Creeping Back

• Leave the slow query log on permanently with a sane threshold (1-2 seconds) and check it monthly rather than only when things break.
• Run ANALYZE TABLE periodically on large, frequently-updated tables so MySQL's query optimizer has accurate statistics to work from.
• Cap post revisions in WordPress (define('WP_POST_REVISIONS', 10); in wp-config.php) instead of letting them grow unbounded.
• Review new plugins before installing — a surprising number of slow-query tickets trace back to a single badly-written plugin query that never got optimized.
• On a VPS, pair this with query caching (Redis or the MySQL query cache on older MariaDB versions) once the underlying queries are actually fast — caching a slow query just hides the problem until the cache expires.

Frequently Asked Questions

Will enabling the slow query log slow my server down further?

The overhead is minimal at a threshold of 1 second or higher — you're only logging queries that were already slow, not every query. Leaving it on with long_query_time = 1 is safe for production. Only very low thresholds (under 0.1s) on a high-traffic site start adding noticeable logging overhead.

I don't have SSH access on my shared cPanel plan — can I still find slow queries?

Yes. phpMyAdmin's Status > Monitor tab shows currently running and recently completed queries with timing, and most hosts (including SkyServer) can enable the slow query log for your account on request and send you the log file directly.

How do I know which index to add if EXPLAIN shows multiple possible columns?

Start with the column used in the WHERE clause with the highest selectivity — the one that narrows the result set down the most. For composite indexes, order columns by how the query filters: equality checks first, range checks (like a date comparison) last.

Can too many indexes cause performance problems?

Yes. Every index has to be updated on every INSERT, UPDATE, and DELETE, so a table with a dozen unused indexes slows down writes and bloats table size for no read benefit. Check SHOW INDEX FROM tablename; periodically and drop indexes that never show up in your slow query analysis.

Is this the same thing as the "Too Many Connections" error?

No, they're related but different problems. Too many connections means MySQL has run out of connection slots, usually from unclosed connections or a traffic spike. Slow queries mean the queries themselves take too long to execute — and slow queries can actually cause connection exhaustion too, since connections stay open longer waiting for a query to finish.