How to Identify and Fix Slow MySQL Queries Using the Slow Query Log, Explain Analyze, and Performance Schema on Ubuntu Server

ByAkensai

Knowing how to identify and fix slow MySQL queries using the slow query log, EXPLAIN ANALYZE, and Performance Schema on Ubuntu Server is one of the most valuable skills a server administrator can have. Slow queries are a leading cause of poor website performance. They cause page timeouts, frustrated users, and unnecessary server load. Left unchecked, they can bring down a busy WordPress site or web application entirely. In this tutorial, you will learn how to enable and read the slow query log, use EXPLAIN ANALYZE to break down query execution, and query the Performance Schema for deeper insight. By the end, you will have a clear process for tracking down the exact queries that are hurting your database performance and fixing them efficiently.

Prerequisites and Requirements for Fixing Slow MySQL Queries

Before you start, make sure you have the following in place.

Required access and software:

  • Ubuntu Server 20.04 or 22.04 (with sudo privileges)
  • MySQL 8.0 installed and running
  • SSH access to your server
  • A database with real or test data to query against

Assumed knowledge level:
You should be comfortable running Linux commands and have basic familiarity with MySQL. You don’t need to be a database expert. This tutorial explains each step clearly.

Estimated time to complete:
Around 30 to 45 minutes, depending on your server setup and how many queries you investigate.

You should also have a text editor available, such as nano or vim, for editing MySQL configuration files. All commands in this tutorial are run as a sudo user unless otherwise noted.

How to Identify Slow MySQL Queries Using the Slow Query Log and Performance Schema

You might also find this useful: How to Manage ZFS Datasets, Snapshots, and Replication on Ubuntu Server

This section walks you through enabling the slow query log, reading its output, and using the Performance Schema to find problem queries.

Step 1: Enable the MySQL Slow Query Log

Open the MySQL configuration file on Ubuntu.

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Find the [mysqld] section and add the following lines. If they already exist, uncomment them.

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

The long_query_time value is in seconds. Setting it to 1 logs any query taking longer than one second. Save the file and restart MySQL.

sudo systemctl restart mysql

Step 2: Verify the Slow Query Log is Active

Log into MySQL and confirm the settings took effect.

sudo mysql -u root -p

Then run this inside the MySQL shell.

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

You should see slow_query_log set to ON and the correct log file path listed.

Step 3: Read the Slow Query Log

After your application runs for a while, check the log file.

sudo tail -100 /var/log/mysql/mysql-slow.log

Each entry shows the query time, lock time, rows examined, and the full SQL statement. Focus on queries with high Query_time or high Rows_examined values. Those are your targets.

You can also use mysqldumpslow to summarize the log.

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

This shows the top 10 slowest queries sorted by time. It’s a fast way to spot patterns.

Step 4: Query the Performance Schema for Slow Statements

The MySQL Performance Schema gives you real-time data on query execution. Run this inside the MySQL shell.

SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1000000000 AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

This returns the top 10 slowest query patterns with their average execution time in milliseconds. The DIGEST_TEXT column shows a normalized version of the query. Use it to identify which queries need attention.

How to Fix Slow MySQL Queries Using EXPLAIN ANALYZE

Once you know which queries are slow, you need to understand why. EXPLAIN ANALYZE is your best tool for this.

Step 5: Run EXPLAIN ANALYZE on a Slow Query

Take a slow query from your log and prefix it with EXPLAIN ANALYZE. Here’s a basic example.

EXPLAIN ANALYZE SELECT  FROM wp_posts WHERE post_status = 'publish' AND post_type = 'post';

MySQL will execute the query and return a full breakdown of how it ran. Look for these warning signs in the output.

  • Full table scan , MySQL reads every row instead of using an index
  • High actual rows , The query scanned far more rows than it returned
  • Nested loop joins , Can be slow with large tables if indexes are missing

Step 6: Add Indexes to Fix the Problem

If EXPLAIN ANALYZE shows a full table scan, adding an index usually solves it. For the example above, add a composite index.

ALTER TABLE wp_posts ADD INDEX idx_status_type (post_status, post_type);

Run EXPLAIN ANALYZE again after adding the index. You should see the query now uses the index and scans far fewer rows. The execution time should drop significantly.

Step 7: Rewrite Inefficient Queries

Sometimes an index isn’t enough. A poorly written query may need restructuring. Avoid using SELECT when you only need specific columns. Replace this:

SELECT  FROM wp_postmeta WHERE meta_key = '_thumbnail_id';

With this:

SELECT post_id, meta_value FROM wp_postmeta WHERE meta_key = '_thumbnail_id';

Fetching only the columns you need reduces memory usage and speeds up query execution. For WordPress sites, also check if your queries use WP_Query arguments correctly. Poorly structured WP_Query calls are a common source of slow database queries.

Troubleshooting Common Issues When Fixing Slow MySQL Queries

The slow query log file is empty

Check that MySQL actually restarted successfully after your config change.

sudo systemctl status mysql

Also confirm the log directory exists and MySQL has write permission.

sudo ls -la /var/log/mysql/

If the directory doesn’t exist, create it.

sudo mkdir -p /var/log/mysql
sudo chown mysql:mysql /var/log/mysql

EXPLAIN ANALYZE returns an error

EXPLAIN ANALYZE requires MySQL 8.0.18 or later. Check your version first.

mysql --version

If you’re on an older version, use EXPLAIN FORMAT=JSON as an alternative. It provides similar detail without requiring MySQL 8.0.18+.

Performance Schema tables are empty

The Performance Schema is enabled by default in MySQL 8.0. If your tables are empty, check that consumers are enabled.

SELECT  FROM performance_schema.setup_consumers WHERE NAME LIKE '%statements%';

If any are disabled, enable them.

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%statements%';

Query is still slow after adding an index

Run ANALYZE TABLE to update table statistics. MySQL sometimes ignores a new index if its statistics are stale.

ANALYZE TABLE wp_posts;

Also check if the query uses a function on an indexed column. Functions like LOWER() or DATE() applied directly to a column prevent MySQL from using the index on that column.

For more guidance on Ubuntu server management, the official Ubuntu Server documentation is an excellent reference.

Conclusion

You now know how to identify and fix slow MySQL queries using the slow query log, EXPLAIN ANALYZE, and Performance Schema on Ubuntu Server. You enabled logging, read the output, queried real-time performance data, and used EXPLAIN ANALYZE to pinpoint and resolve inefficient queries. These tools work together to give you a complete picture of what’s happening inside your database. Start with the slow query log to find problem queries. Use EXPLAIN ANALYZE to understand why they’re slow. Then fix them with indexes or query rewrites. As a next step, consider setting up automated slow query alerts or exploring MySQL query caching strategies. Regular database performance reviews will keep your server running fast and your users happy.

Similar Posts