How to find the slow database queries behind a slow PHP site (MySQL and MariaDB)

Before you start

• Check PHP first. If every page is slow, even simple ones, make sure OPcache is on and PHP-FPM has enough workers. Our guide to making PHP faster with OPcache and PHP-FPM covers that, and its PHP-FPM slow log often points at the database. That's when this guide helps.
• Take a backup of the database before you change tables. See our backup guide.
• See which database you run: mysql --version. It says MariaDB or MySQL in the output. Ubuntu 24.04 offers both (MySQL 8.0 and MariaDB 10.11). Debian 13 comes with MariaDB 11.8.
• The examples use a WordPress database called shop. Change the name to yours. If your tables don't start with wp_, change that too.

1. Turn on the slow query log

Don't edit the big config file. Put your settings in a small file of their own, so updates don't touch them and you can remove them later in one step. MySQL:

sudo nano /etc/mysql/mysql.conf.d/99-slow-log.cnf

MariaDB:

sudo nano /etc/mysql/mariadb.conf.d/99-slow-log.cnf

Put this in it (the same for both):

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

long_query_time = 1 means "write down every query that takes longer than 1 second". The default is 10 seconds, which is far too long for a web page. Newer MariaDB also knows these settings as log_slow_query, log_slow_query_file and log_slow_query_time. Both names work.

On Debian 13, make the log folder first. It doesn't exist there, and MariaDB won't create it. Ubuntu already has it.

sudo install -d -o mysql -g adm -m 750 /var/log/mysql

Restart the database (a few seconds without it) and check:

sudo systemctl restart mysql      # MariaDB: sudo systemctl restart mariadb
sudo mysql -e "SHOW GLOBAL VARIABLES LIKE 'slow_query%'; SHOW GLOBAL VARIABLES LIKE 'long_query_time';"

You should see slow_query_log ON, your file name and 1.000000.

Can't restart right now? You can switch it on while the database runs. This lasts until the next restart, so keep the file from above as well:

sudo mysql -e "SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log'; SET GLOBAL long_query_time = 1; SET GLOBAL slow_query_log = ON;"

A new long_query_time only counts for new connections. PHP opens a new one for almost every page, so that's fine.

2. Where the log is, and who can read it

The log is /var/log/mysql/slow.log. Only the mysql user and the adm group can read it, so read it with sudo:

sudo tail -n 30 /var/log/mysql/slow.log

Keep it private. The log holds whole queries, with the values in them: email addresses, names, sometimes password hashes. Don't make it readable for everyone, and never copy it into a website folder. On Ubuntu, keep it inside /var/log/mysql/: MySQL's AppArmor rules only let it write there.

Now use the site normally for a while, or open the pages that feel slow. If the log is still empty after a day, lower the limit to 0.5 or even 0.2 seconds.

3. Read the summary

The raw log repeats the same query again and again. A small tool groups them and adds up the time. It has a different name depending on what you run:

• MySQL (Ubuntu): mysqldumpslow
• MariaDB 10.11 (Ubuntu 24.04): mariadb-dumpslow, and mysqldumpslow works too
• MariaDB 11.8 (Debian 13): only mariadb-dumpslow

Show the 5 queries that took the most time in total:

sudo mysqldumpslow -s t -t 5 /var/log/mysql/slow.log
sudo mariadb-dumpslow -s t -t 5 /var/log/mysql/slow.log

Numbers become N and text becomes 'S', so the same query with different values counts as one. This is from our test site on MariaDB:

Count: 4  Time=0.37s (1s)  Lock=0.00s (0s)  Rows_sent=1.0 (4), Rows_examined=810006.0 (3240024), ...
  SELECT post_id FROM wp_postmeta WHERE meta_key = 'S' AND meta_value = 'S'

How to read it: the query ran 4 times and took 0.37 seconds each time. It sent back 1 row but had to look at 810,006 rows to find it. That gap is the clue. A query that reads hundreds of thousands of rows to return one is missing an index.

Both tools printed a line like Died at /usr/bin/mysqldumpslow line 163 at the end. The summary above it was complete, so you can ignore it.

4. Ask the database how it runs the query

First, what's an index? Think of the index at the back of a book. To find every page about "PHP", you look it up and jump straight to the right pages. Without it, you read the whole book. A database table without the right index is the book without one: the database reads every row.

Put EXPLAIN in front of the slow query (copy it from the raw log, with the real values) and run it in the site's database:

sudo mysql shop -e "EXPLAIN SELECT post_id FROM wp_postmeta WHERE meta_key = '_sku' AND meta_value = 'SKU-0123456'\G"

EXPLAIN doesn't run the query, so it's safe and fast. On MariaDB we got:

        table: wp_postmeta
         type: ALL
possible_keys: meta_key
          key: NULL
         rows: 796436

The lines that matter, in plain words:

• type: how it searches. ALL means it reads the whole table, the bad case. ref, eq_ref, range or const mean it uses an index.
• key: the index it chose. NULL means none.
• rows: about how many rows it expects to read. It's a guess, but small is good and huge is bad.
• possible_keys: indexes it could have used.
• Extra: Using filesort or Using temporary mean extra sorting work. That's fine on a few rows, slow on many.

MySQL chose differently for the same query: type: ref, key: meta_key, rows: 347424. So an index can be in use and the query is still slow. The meta_key index only finds "all _sku rows", and then it checks each of those one by one. The rows number tells you that.

5. Add an index, and measure before and after

Measure first. Run the query a few times and write down the time the client shows. Then add an index on the columns in the WHERE part:

sudo mysql shop -e "ALTER TABLE wp_postmeta ADD INDEX meta_key_value (meta_key(191), meta_value(32));"

meta_value can hold very long text, so the index only covers its first 32 characters. That's enough to find short values like a product number. Then EXPLAIN again:

         type: ref
          key: meta_key_value
         rows: 1

From about 800,000 rows down to 1. Run the query again and compare the times.

Our numbers, from a small test: a WordPress site in a container on a shared lab machine, with 200,001 posts and 810,006 wp_postmeta rows, searching for one product number through WP_Query. Treat them as a rough idea, not a promise:

• MariaDB 11.8 (Debian 13): 0.33 to 0.42 seconds before, 0.001 seconds after. Adding the index took 1.8 seconds.
• MySQL 8.0 (Ubuntu 24.04): 0.29 to 0.37 seconds before, 0.001 to 0.002 seconds after. Adding the index took 7.6 seconds.

After the index, the query no longer showed up in the slow log.

Good to know before you add indexes:

• Adding an index to a big table takes time and puts load on the server. Reads and writes keep working on InnoDB tables, but do it at a quiet time.
• Each index uses disk space and makes writes a little slower. Add one for a query you saw in the log, not "just in case" for every column.
• Changing a table that belongs to WordPress, a plugin or your framework is your own change. Write it down, and check with SHOW INDEX FROM wp_postmeta; after big updates. To remove it again: ALTER TABLE wp_postmeta DROP INDEX meta_key_value;
• Sometimes the better fix is in the code: ask for less, or save the result for a while.

6. WordPress: two common problems

Searching in wp_postmeta. WordPress keeps extra fields (prices, product numbers, settings of page builders) in wp_postmeta. Out of the box it only has an index on meta_key, not on meta_value. So any plugin or theme that searches by value, like "find the product with this number", reads many rows. That's exactly what we fixed above. If you'd rather not change tables by hand, the plugin Index WP MySQL For Speed adds indexes like this for you. The Query Monitor plugin shows every query a page makes, and which plugin made it.

Big "autoloaded" options. WordPress loads every option marked "autoload" on every page, even when the page doesn't need it. Plugins sometimes put large things there and forget them. This finds the biggest:

sudo mysql shop -e "SELECT option_name, LENGTH(option_value) AS bytes FROM wp_options WHERE autoload IN ('yes','on','auto-on','auto') ORDER BY bytes DESC LIMIT 10;"

And the total:

sudo mysql shop -e "SELECT COUNT(*) AS options, ROUND(SUM(LENGTH(option_value))/1024) AS kb FROM wp_options WHERE autoload IN ('yes','on','auto-on','auto');"

A fresh WordPress came to about 30 KB in our test. If you see several megabytes, look at the names: they usually tell you which plugin they belong to. With WP-CLI you can stop loading one on every page without deleting it:

wp option set-autoload some_big_option off

Run WP-CLI as the site's own user, not as root. Delete options only when you know the plugin is gone. In our small test, one 1.5 MB autoloaded option made WordPress load its options in about 2 to 3 milliseconds instead of 1. That's small on its own, but it happens on every page, and it grows with each big option.

7. Laravel: the "N+1" problem

The most common slow spot in Laravel isn't one slow query, but many small ones. Say a page lists 50 posts and shows each author's name. Written the simple way, Laravel makes 1 query for the posts and then 50 more, one per author. Each is fast, so none shows up in the slow log, but together they make the page slow. The fix is to load the authors together with the posts:

$posts = Post::with('author')->get();

Laravel can warn you about this while you develop. In app/Providers/AppServiceProvider.php, in the boot() method:

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

// Throw an error for N+1 loading, but not on the live site
Model::preventLazyLoading(! $this->app->isProduction());

// Write queries slower than 500 ms to the Laravel log
DB::listen(function ($query) {
    if ($query->time> 500) {
        Log::warning('Slow query', ['sql' => $query->sql, 'ms' => $query->time]);
    }
});

See Laravel's docs on preventing lazy loading and listening for query events. We didn't test these Laravel examples for this guide.

8. Find a query that is stuck right now

If the site hangs at this moment, look at what the database is doing:

sudo mysql -e "SHOW FULL PROCESSLIST;"

Each line is one connection. Time is how many seconds the current query has been running, and Info is the query itself. Normal queries finish in well under a second, so look for big numbers in Time. A test query that hung looked like this:

Id   User  Host       db    Command  Time  State       Info
69   root  localhost  shop  Query    2     User sleep  SELECT SLEEP(20)

You can stop a query with its Id:

sudo mysql -e "KILL 69;"

Only stop queries you understand. Stopping a big UPDATE or ALTER TABLE halfway makes the database undo it, which can take as long again. Save the query text first, so you can EXPLAIN it later.

9. "Queries not using indexes": use with care

There's one more setting, log_queries_not_using_indexes. It also writes down queries that don't use an index, however fast they are. It sounds useful, but it's noisy. Many small queries read a short table without an index, and that's fine. In our test, opening a few simple WordPress pages 5 times added 17 entries on MariaDB and 24 on MySQL. Every one of them took less than 1 millisecond.

If you try it, switch it on for a short time only, and turn it off again:

sudo mysql -e "SET GLOBAL log_queries_not_using_indexes = ON;"
# use the site for a few minutes, then:
sudo mysql -e "SET GLOBAL log_queries_not_using_indexes = OFF;"

10. Turn it off, or keep it small

Once you've fixed what you found, you can turn the log off without a restart:

sudo mysql -e "SET GLOBAL slow_query_log = OFF;"

To keep it off after a restart, delete your 99-slow-log.cnf file. Or keep the log on with a 1-second limit as an early warning. Then make sure it can't grow forever. Both systems already rotate every .log file in /var/log/mysql/:

• Ubuntu with MySQL (/etc/logrotate.d/mysql-server): every day, keeps 7 old files.
• Debian with MariaDB (/etc/logrotate.d/mariadb): once a month, and only when the file is at least 50 MB (at 500 MB it rotates right away). It keeps 6 old files.

That's why the log file in step 1 ends in .log. A name without it isn't rotated. After moving the log aside by hand, tell the database to start a new file: sudo mysql -e "FLUSH SLOW LOGS;". Our guide to keeping logs from filling the disk explains logrotate in more detail.

If something goes wrong

• The database doesn't start after step 1: look at sudo journalctl -u mariadb -n 30 (or -u mysql). On Debian, the usual cause is the missing /var/log/mysql folder. A typo in the file also stops it; move the file away and start again.
• The log stays empty: check the settings with the SHOW GLOBAL VARIABLES command from step 1, and lower long_query_time. Maybe your queries are fast and the slowness is somewhere else, for example in PHP or a call to another website.
• "Permission denied" when reading the log: use sudo. Don't change the file's permissions.
• The index didn't help: run EXPLAIN again and check key. If it still says NULL, the index doesn't match the WHERE part, for example because the query uses LIKE '%word%', which can't use a normal index.
• The disk is filling up: the limit is too low, or log_queries_not_using_indexes is still on. Raise the limit, turn the extra setting off, and see step 10.

Official sources

• Slow query log: MySQL 8.0 and MariaDB
• Summary tools: mysqldumpslow (MySQL) and mariadb-dumpslow (MariaDB)
• EXPLAIN: EXPLAIN output (MySQL) and EXPLAIN (MariaDB)
• How MySQL uses indexes (MySQL)
• SHOW PROCESSLIST: MySQL and MariaDB
• Autoload changes for large options (WordPress core blog)

Next steps: make sure the database itself is set up safely with our guide to keeping MySQL and MariaDB safe, and check that it has enough memory with our guide to stopping a small server from running out of memory.

ServerNest is in development. Slow query reports aren't part of it today. Join early access to follow along.

← Back to the security blog