When your WordPress dashboard takes twenty seconds to load or a customer report times out, the database is usually the problem. MySQL performance issues creep up on everyone—beginners and veterans alike—but beginners make a handful of predictable mistakes that mask the real bottleneck. I've watched dozens of site owners chase phantom problems because they never turned on the slow query log or misread an EXPLAIN plan. This guide walks you through the first working setup, the mistakes that waste your time, and the fixes that actually move the needle.
What is a slow MySQL query and why does it matter?
A slow query is any SQL statement that takes longer than a threshold you define—commonly one or two seconds—to return results. MySQL executes hundreds or thousands of queries every minute on a busy server. Most finish in milliseconds. A single query that scans a million-row table without an index can lock up your entire application while users wait.
Slow queries burn CPU, hold table locks, and pile up connections until MySQL hits its connection limit and refuses new requests. Your hosting dashboard might show 100% CPU with no obvious cause. The root is almost always a handful of bad queries running over and over.
Mistake 1: Not enabling the slow query log
MySQL ships with the slow query log disabled. Until you turn it on, you're flying blind. You might guess which page is slow, but you won't know which SELECT or UPDATE is the culprit.
Check the current state by logging into MySQL as root:
mysql -u root -p
Then run:
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
If slow_query_log is OFF, you're not logging anything. The long_query_time variable sets the threshold in seconds; the default is often 10, which is too high for a web application. Most support engineers I know set it between 1 and 2 seconds.
Enable the log and lower the threshold:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
Those changes apply immediately but disappear on restart. Make them permanent by editing your MySQL configuration file. On most Linux distributions that file lives at /etc/my.cnf or /etc/mysql/my.cnf. Add these lines under the [mysqld] section:
[mysqld]
slow_query_log = 1
long_query_time = 2
slow_query_log_file = /var/log/mysql/mysql-slow.log
Restart MySQL:
systemctl restart mysqld
or
systemctl restart mysql
depending on your distribution. Now every query slower than two seconds lands in /var/log/mysql/mysql-slow.log.
Mistake 2: Ignoring the slow query log after you enable it
Turning on the log is step one. Reading it is step two. I've seen servers with gigabyte slow-query logs that nobody ever opened.
Tail the log in real time while you reproduce the slow page:
tail -f /var/log/mysql/mysql-slow.log
Each entry shows the query text, how long it took, how many rows it examined, and the timestamp. Look for queries with high execution time or a massive row count examined compared to rows returned. If MySQL scans 500,000 rows to return 10, you have a missing index.
MySQL ships with mysqldumpslow, a Perl script that summarizes the log. Run it like this:
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log
The -s t flag sorts by total time, and -t 10 shows the top ten queries. You'll see the same query pattern grouped together with an execution count. Focus on the queries that appear hundreds of times—fixing one bad query that runs constantly has more impact than optimizing a rare report.
Mistake 3: Skipping EXPLAIN and guessing at the fix
Once you have a suspect query, the next mistake is changing things at random—adding an index on every column, rewriting the WHERE clause five different ways—without understanding what MySQL is actually doing.
The EXPLAIN command shows MySQL's execution plan. Copy the slow query from your log, paste it into a MySQL prompt, and prepend the word EXPLAIN:
EXPLAIN SELECT * FROM wp_posts WHERE post_status = 'publish' AND post_type = 'post' ORDER BY post_date DESC LIMIT 10;
EXPLAIN returns a table with columns like type, possible_keys, key, rows, and Extra. Here's what matters:
- type: ALL means a full table scan (bad). Index or range is better. Const or eq_ref is best.
- key: which index MySQL chose. NULL means no index was used.
- rows: how many rows MySQL estimates it will examine. If this number is close to your entire table size and you're only returning a handful of rows, you need an index.
- Extra: look for "Using filesort" or "Using temporary". Both signal expensive operations that slow things down.
A full table scan on a hundred-row table is fine. On a million-row table it will crush your server.
Mistake 4: Creating the wrong index or too many indexes
Seeing NULL in the key column makes beginners add an index on the first column they see in the WHERE clause. That sometimes helps and sometimes does nothing.
Indexes work left-to-right. If your query filters on post_status and post_type, then sorts by post_date, you want a composite index in that order:
CREATE INDEX idx_status_type_date ON wp_posts(post_status, post_type, post_date);
MySQL can use this index to satisfy the WHERE conditions and the ORDER BY in one pass. If you create three separate single-column indexes instead, MySQL will pick one and ignore the others or merge them inefficiently.
Don't index everything. Every index speeds up reads but slows down writes because MySQL has to update the index on every INSERT, UPDATE, or DELETE. I've debugged WordPress sites with forty redundant indexes on wp_posts where the real problem was two missing composite indexes. Run SHOW INDEXES FROM tablename; and drop duplicates or unused indexes.
What if EXPLAIN looks fine but the query is still slow?
Sometimes the execution plan shows an index in use, low row count, and no filesort, but the query still takes three seconds. Two common causes: table locks and suboptimal query structure.
MySQL's default storage engine InnoDB uses row-level locking, but MyISAM locks the entire table. Check your engine:
SHOW TABLE STATUS WHERE Name = 'your_table_name';
If the Engine column says MyISAM, convert to InnoDB:
ALTER TABLE your_table_name ENGINE=InnoDB;
This operation locks the table while it runs, so schedule it during low traffic.
Subqueries in the SELECT list run once per row and kill performance. Rewrite them as JOINs. For example, instead of:
SELECT post_title, (SELECT COUNT(*) FROM wp_comments WHERE comment_post_ID = wp_posts.ID) AS comment_count FROM wp_posts;
use:
SELECT wp_posts.post_title, COUNT(wp_comments.comment_ID) AS comment_count
FROM wp_posts
LEFT JOIN wp_comments ON wp_comments.comment_post_ID = wp_posts.ID
GROUP BY wp_posts.ID;
The JOIN version scans each table once instead of running a subquery for every post.
Mistake 5: Not filtering data early enough
Beginners often fetch everything and filter in application code. A PHP script that runs SELECT * FROM orders and then loops through 100,000 rows to find last month's orders wastes memory and network bandwidth.
Push filters into the SQL WHERE clause and use LIMIT:
SELECT * FROM orders WHERE order_date >= '2026-06-01' AND order_date < '2026-07-01' LIMIT 100;
MySQL's query cache was removed in version 8.0, so you can't rely on it. Fetch only the columns you need instead of SELECT *. If you only display order ID and customer name, write:
SELECT order_id, customer_name FROM orders WHERE ...;
Transferring ten columns instead of fifty cuts network time and memory usage.
Mistake 6: Running SELECT COUNT(*) on huge tables
Counting rows in a large table with SELECT COUNT(*) FROM big_table forces a full table scan unless you add a WHERE clause that matches an index. InnoDB doesn't store row counts in metadata the way MyISAM did, so COUNT queries are expensive.
If you need an approximate count for display purposes, query the information schema:
SELECT table_rows FROM information_schema.tables WHERE table_schema = 'your_database' AND table_name = 'big_table';
The number is an estimate based on statistics and arrives instantly. For exact counts, cache the result in application code or a summary table and update it periodically.
Mistake 7: Not setting up query monitoring long-term
Fixing queries once is good. Catching new slow queries before they cause outages is better. The slow query log grows forever unless you rotate it. Add a logrotate rule in /etc/logrotate.d/mysql:
/var/log/mysql/mysql-slow.log {
daily
rotate 7
missingok
compress
delaycompress
notifempty
create 640 mysql mysql
sharedscripts
postrotate
/usr/bin/mysqladmin flush-logs
endscript
}
This keeps a week of logs and compresses old ones. Tools like pt-query-digest from Percona Toolkit parse the slow log and generate detailed reports. Install it:
wget percona.com/get/pt-query-digest
chmod +x pt-query-digest
./pt-query-digest /var/log/mysql/mysql-slow.log > report.txt
The report groups queries by fingerprint, shows execution statistics, and highlights the worst offenders. Run it weekly and track which queries regress.
How to test your fixes without breaking production
Changing indexes or rewriting queries on a live database is risky. Copy your database to a staging server or local environment first. Use mysqldump to export:
mysqldump -u root -p your_database > backup.sql
Import it on your test server:
mysql -u root -p test_database < backup.sql
Run your new query or add the index on the test copy. Compare EXPLAIN output before and after. Time the query with the mysql client's built-in timer by starting your session with:
mysql -u root -p --show-warnings
and prepending queries with SELECT BENCHMARK(1000, (...)); to run them a thousand times and see average duration. Once you're confident, apply the change to production during a maintenance window.
What to check first when MySQL performance tanks
Your slow query log is the diagnostic map. Enable it, set a reasonable threshold, and let it run for a few hours under normal traffic. Parse the log with mysqldumpslow or pt-query-digest to find the patterns. Run EXPLAIN on the top offenders and look for missing indexes, full table scans, and filesort operations. Add composite indexes that match your WHERE and ORDER BY columns, rewrite subqueries as JOINs, and test on a staging environment before pushing changes live. Monitor query performance over time so regressions get caught early.
Common questions
Do I need to restart MySQL after changing long_query_time?
No. SET GLOBAL applies immediately to new connections. Existing connections keep the old value until they reconnect.
Can I enable the slow query log on shared hosting?
Most shared hosts disable access to MySQL configuration files and global variables. Ask your hosting provider or move to a VPS where you control MySQL.
How do I know if an index is being used?
Run EXPLAIN on your query. If the key column shows your index name, it's in use. If it says NULL, MySQL ignored it.
Will adding indexes slow down my site?
Each index speeds up SELECT queries but adds overhead to INSERT, UPDATE, and DELETE. A few well-chosen indexes help. Dozens of redundant indexes hurt.
What's a safe long_query_time value?
Start with 2 seconds. If you get thousands of log entries, raise it to 5. If you see nothing, lower it to 1. Tune it based on your application's tolerance.