MySQL Slow Query Log: Finding the Queries That Slow Your Website

The MySQL slow query log is a plain record of every database query that took longer than a limit you choose. When a website feels sluggish, the cause is often hidden in a handful of queries that run too long or too often, and the slow query log is the simplest way to find them. It works the same way for MySQL and for MariaDB, and it costs almost nothing to switch on for a while.

This guide shows how to enable the log safely, how to read it, how to summarise thousands of entries into a short list of offenders, and how to fix the typical causes: missing indexes, heavy plugins, repeated queries and oversized tables. It is aimed at administrators of WordPress and other database-driven sites who have shell access to their server, but the reasoning also helps site owners who need to brief a developer.

Why the Database Is Often the Bottleneck

A dynamic page is assembled on request. The web server starts the application, the application asks the database for posts, settings, menus, users and plugin data, and only then can it build the HTML. If the database answers slowly, every visitor waits. Because the same queries run for every request, a query that takes one second instead of ten milliseconds does not just slow one page; it can exhaust the server when traffic rises.

Guessing is unreliable. A page may be slow because of a large image, a third-party script or a slow DNS lookup, rather than the database. Measurements separate those causes. The slow query log provides the database part of the picture: which statements are slow, how long they take, how many rows they examine and how often they run. With that evidence, you can decide whether the right fix is an index, a code change, caching or more memory.

The database is also where small problems compound. A table that grew from ten thousand rows to ten million rows over two years can turn a once-instant query into a daily timeout. Regular review of the log catches this drift early, before customers notice.

Enable the Slow Query Log

The log is controlled by a few settings. You can change them at runtime for a quick investigation, and then add them to the configuration file if you want them to persist after a restart. Always apply changes in a quiet period, and write down the original values so you can restore them.

For a temporary investigation, an administrator with the right privileges can set the variables globally at runtime and then reconnect. Check the official documentation for the exact syntax and defaults for your version: the MySQL slow query log reference and the MariaDB slow query log page describe every option. Begin with a threshold of one second, collect a day or two of traffic and then lower it, because a very low threshold on a busy server creates a large file quickly.

Make sure the log file is not in a web-accessible directory and that its permissions allow only the database user and administrators to read it. Queries can contain personal data such as email addresses, so treat the file as sensitive. Configure log rotation as well; our guide to log rotation and disk space explains how to prevent a growing log from filling the disk.

Read What the Log Contains

Each entry begins with a few comment lines that describe the query: the time, the user and host, the query time, the lock time, the number of rows sent and the number of rows examined. The statement itself follows. Four fields tell you most of what you need.

• Query_time. How long the statement ran in total.
• Lock_time. How long it waited for locks. High values point to contention between queries rather than a slow query alone.
• Rows_sent. How many rows were returned to the application.
• Rows_examined. How many rows the database read to produce them.

A large gap between rows examined and rows sent is a classic warning. A query that reads two million rows to return twenty is probably missing a useful index or filtering in the wrong place. A query that returns hundreds of thousands of rows may be asking for too much data, such as loading all posts when the page shows ten.

Do not treat one slow entry as a crisis. Backups, imports and scheduled jobs often produce legitimate long queries, usually at night. Look at the time stamps and compare with your cron schedule before deciding that something is wrong.

Summarise Thousands of Entries Into a Short List

A raw log is too long to read by eye. Use a summarising tool that groups similar statements, ignoring the literal values, and ranks them by total time. MySQL ships a simple one, mysqldumpslow, and the open-source Percona Toolkit provides a more detailed one, pt-query-digest. Both produce a ranked report of query “fingerprints” with counts, average and maximum times and examples.

Sort by total time, not by the slowest single run. A query that takes 0.3 seconds but runs fifty thousand times a day consumes far more server time than a query that takes ten seconds once. The top three to five entries in that ranking usually account for most of the load, and fixing them yields the biggest gain.

1. Collect a representative period. Include a normal working day and, if possible, a peak.
2. Run the summary tool. Save the report with the date.
3. Pick the top entries by total time. Note the table names and what each query seems to do.
4. Trace each one to its source. Which plugin, theme function or report generates it?
5. Choose the fix. Index, rewrite, cache or remove.
6. Measure again. Compare a new log period with the old one.

Use EXPLAIN to See Why a Query Is Slow

When you have a suspect statement, ask the database how it plans to run it. Prefixing the query with EXPLAIN returns a table describing which indexes it considers, which it uses and roughly how many rows it expects to read. The EXPLAIN documentation describes each column. Run it on a copy of the data or on a read-only account, and avoid running heavy statements repeatedly on a live system during busy hours.

Look for signs of full table scans, where the plan reads every row of a large table, and for temporary tables or file sorting on big result sets. These often indicate a missing or unsuitable index. If the plan shows an index but the query is still slow, check whether the index fits the actual conditions: the order of columns in a combined index matters, and functions applied to a column in the condition can prevent its use.

Typical Causes and Their Fixes

Most slow queries on business websites fall into a few patterns. Knowing them speeds up diagnosis.

• Missing index. Add an index that matches the filter and sort columns. Test on a copy first, because indexes use disk space and slow down writes slightly.
• Oversized options table. In WordPress, a large amount of automatically loaded data in the options table is read on every request. Remove data left by uninstalled plugins and stop heavy data from auto-loading.
• Heavy meta queries. Searches over custom fields can become slow on large sites. Consider a dedicated table or an index suited to the lookup.
• Repeated identical queries. The same statement running dozens of times per page suggests a loop in code. Fix the code or add object caching; see our article on object caching for WordPress.
• Unbounded result sets. A query without a limit can return far more than the page needs. Add pagination.
• Locking. Long write operations can block reads. Schedule imports for quiet hours and keep transactions short.
• Insufficient memory. If the database keeps reading from disk because its buffers are too small, tuning memory settings may help; check capacity as described in server capacity planning.

For database-specific advice on stores, read WordPress database optimization for WooCommerce.

A Worked Example

Imagine a small shop whose product category pages take four seconds to load. The administrator enables the log with a one-second threshold for a day and summarises it. The top entry, responsible for most of the total time, is a query that filters products by a custom field and sorts them by price. It runs on every category page view and examines about 900,000 rows to return 24.

EXPLAIN confirms a full scan of the meta table. The administrator adds a suitable index on a staging copy, checks that the plan now reads a few hundred rows and measures the page. Load time falls to under a second. After a backup, the same index is added to the live database in a quiet hour. The next day’s log shows the query has dropped out of the top ten. The whole job took an afternoon, and it began with evidence rather than guesswork, which is exactly what the log is for.

Make It a Routine, Then Turn It Down

Treat the slow query log as an inspection tool, not a permanent fixture at maximum detail. After you have fixed the main offenders, raise the threshold to a modest value, such as one second, and leave it on at that level so that new problems appear without producing a flood. Review the summary monthly or after any major plugin, theme or traffic change, and note the top queries in your maintenance records.

Always take a backup before changing indexes or table structures, and test changes on a staging copy. Our MySQL backup guide explains how to create and test restores. If you prefer to have an administrator run these checks regularly, our server administration service includes performance reviews of databases and web servers.

Frequently Asked Questions

Does the slow query log slow down my site?

Writing entries has a small cost, which is usually negligible at a threshold of one second. A very low threshold or logging every query without indexes on a busy server can add load and fill the disk, so use those settings briefly.

What threshold should I use for long_query_time?

Start at one second to find the worst offenders, then lower it to half a second or less for a day or two when you want a more detailed picture. Return to a modest value for routine monitoring.

Can I use the log on shared hosting?

Often not, because you may not have the privileges to change global settings. Ask your host whether it offers query logs or a performance panel, or use a plugin that records slow queries for your application.

Is every slow query a problem?

No. Backups, imports and reports can legitimately run for a long time. Look at when they run and whether they affect visitors before deciding to change anything.

Will adding an index always make a query faster?

No. An index must match the conditions and sort order of the query. Unneeded indexes also use space and slow down inserts and updates, so test each one with EXPLAIN and measure the result.

How do I know the fix worked?

Compare a new log period with the old one: the query should drop in the ranking or disappear, and page response times should improve in your monitoring.

The Bottom Line

The MySQL slow query log turns a vague feeling that the site is slow into a ranked list of specific statements. Enable it with a one-second threshold, summarise the results by total time, examine the top entries with EXPLAIN and fix the cause, whether that is an index, a code change or caching. Protect the log file, rotate it, test changes on a copy and measure again afterwards. A short monthly review keeps your database fast as the site grows.