The Developer’s Guide to Identifying and Optimizing Slow MySQL Queries

This tutorial will guide you through identifying, extracting, and analyzing your most problematic database queries using the command line on a WHM/cPanel server.

If you’re using UltimateWB for your backend, chances are you won’t need any of this. We optimize UltimateWB to run fast whether your website is small or large. But in case you need it – for example, if you are adding complex custom code or building a project from scratch – this guide has you covered.

Related: How to Lazy Load Google Adsense Ads for a Fast Website

Why Avoiding Third-Party Plugins Makes Your Website Faster, Safer, and Easier to Manage

Part 1: Prerequisites

To follow this guide, you must have root access to your server via SSH.

1. Open your terminal and log in: ssh root@your-server-ip.
2. Confirm your log path: In this example, the path is /var/log/mysql-slow.log.
3. Ensure logging is active: If that file is empty, MySQL isn’t recording slow queries yet. You can enable it temporarily without restarting MySQL by running:
   
   mysql -e "SET GLOBAL slow_query_log = 'ON';" mysql -e "SET GLOBAL long_query_time = 2;"
   
   (Note: This sets the threshold to 2 seconds. Any query taking longer than this will be logged.)

Part 2: Extracting the Top 10 (Quick Method)

The mysqldumpslow tool is pre-installed on almost all WHM servers. To get the exact queries (including the actual IDs and data), we use the -a (across) flag.

Command for Top 10 by Average Speed:

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

Command for Top 10 by “Total Impact”: This identifies queries that might be slightly faster but run so often they slow down the entire server.

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

Tip: If your slow query logs are very long and gets cut off, add | less -S to the end, to be able to scroll up/down, as in:

mysqldumpslow -a -s at -t 10 /var/log/mysql-slow.log | less -S

| less opens the output in an interactive viewer where you can use your Up/Down arrow keys, Page Up, and Page Down. Press q to exit back to the terminal.

-S disables line wrapping, making long queries scroll horizontally using your left/right arrow keys instead of wrapping messily onto 20 separate lines.

Part 3: Advanced Analysis (Professional Method)

For a deep dive that provides specific optimization metrics, administrators often use the Percona Toolkit. This is the industry standard for managing high-traffic sites and massive databases.

1. Install the Toolkit: yum install percona-toolkit -y
2. Generate the Digest: pt-query-digest /var/log/mysql-slow.log > /root/slow_report.txt
3. Read the Report: less -S /root/slow_report.txt

What to look for in the report:

• Query ID: A unique fingerprint for that specific type of query.
• Exec time: Look for the “95%” column to see how slow the query is for the vast majority of users.
• V/M (Variance-to-Mean): If this is high, the query is “unstable” (sometimes fast, sometimes very slow).
• The Sample: Scroll down to find the literal SQL statement you can copy-paste into your database manager to test.

Part 4: How to Optimize the Results

Once you have the exact query, the next step is to ask the database why it is slow by running an EXPLAIN plan.

1. Copy the exact query (e.g., SELECT * FROM users WHERE email = 'test@example.com';).
2. Log into MySQL: Type mysql.
3. Run an EXPLAIN:SQLEXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

The Optimization Checklist:

• If type is ALL: This is a “Full Table Scan.” You are missing an Index on the column in your WHERE clause.
• If rows is huge: The query is searching through thousands of rows just to find a few. Adding an index will reduce this number significantly.
• If Using temporary or Using filesort: Your GROUP BY or ORDER BY statements are inefficient and should be optimized.

💡 Ready to go deeper? Now that you know how to find the queries, learn the advanced logic of how to fix them – including the “ESR Rule” for perfect indexing – in our Ultimate Guide to MySQL Query Optimization.

Part 5: Resetting Your Baseline

Once you have optimized your queries (e.g., added your indexes or rewritten your code), you should clear the slow query log. Clearing the old data flushes out the historical noise so you can see if your optimizations actually worked, rather than scrolling through old, pre-optimization data.

On a WHM/cPanel server, you can safely empty the file and tell MySQL to start writing to a fresh slate without restarting the database service.

Run these two commands in your terminal:

# Truncate the log file to 0 bytes
truncate -s 0 /var/log/mysql-slow.log

# Tell MySQL to reopen its log files
mysql -e "FLUSH LOGS;"

Now, you can use tail -f /var/log/mysql-slow.log to watch the log in real time and verify that your optimized queries are no longer triggering the 2-second threshold.

Summary Table of Commands

Goal	Command
Quick Top 10 (Simple)	mysqldumpslow -a -s at -t 10 /var/log/mysql-slow.log mysqldumpslow -a -s at -t 10 /var/log/mysql-slow.log | less -S
Deep Analysis (Pro)	pt-query-digest /var/log/mysql-slow.log
Real-time Monitoring	tail -f /var/log/mysql-slow.log
Test Performance	EXPLAIN [your_query_here];
Reset/Clear	truncate -s 0 /var/log/mysql-slow.log && mysql -e "FLUSH LOGS;"

---

How to Save and Download your Slow Query Log

Here is the step-by-step tutorial to run your slow query report, save it securely above your web root (so it’s completely hidden from the public internet), and view or download it safely via cPanel/WHM File Manager or the terminal.

Step 1: Navigate to your user’s home directory

Log into your terminal (via SSH or WHM Terminal) and change your directory to your specific cPanel account’s home folder.

Replace yourusername with your actual cPanel username:

cd /home/yourusername

(This places the file in a secure private directory that the web server cannot access via URL).

Step 2: Verify your current directory

Confirm you are in the right folder by running:

pwd

The output should look exactly like /home/yourusername.

Step 3: Run mysqldumpslow and save the file securely

Run your slow query command and redirect the output into your private home folder:

mysqldumpslow -a -s at -t 10 /var/log/mysql-slow.log > slow_query_output.txt

Step 4: Access and download your file safely

Now that the file is safely generated, you can access it using one of two secure methods:

• Method A: Via cPanel File Manager (For easy downloading)
  1. Log into your cPanel or WHM File Manager for that account.
  2. Navigate up one level from public_html to your main home directory (/home/yourusername).
  3. Locate slow_query_output.txt, right-click it, and click Download to view it locally on your computer.
  4. Once downloaded, you can safely delete it from the server using the File Manager.
• Method B: Directly in the Terminal (Without downloading) If you just want to read it right away without leaving the command line, use less to view it page-by-page: less slow_query_output.txt (Use your Up/Down arrow keys to scroll, and press q to exit).

---

Related:

The Ultimate Guide to MySQL Query Optimization

Why Should You Care About Your Website Performance Scores on PageSpeed Insights, Even If Your Website Loads Fast?

Does Faster Website Speed Increase Googlebot Crawl Frequency?

---

Looking for a website builder that is flexible enough for a developer but easy enough for a beginner? Learn more about UltimateWB! We also offer web design packages if you would like your website designed and built for you.

Got a techy/website question? Whether it’s about UltimateWB or another website builder, web hosting, or other aspects of websites, just send in your question in the “Ask David!” form. We will email you when the answer is posted on the UltimateWB “Ask David!” section.