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:Bashmysql -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:

Bash

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.

Bash

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

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:Bashyum install percona-toolkit -y
2. Generate the Digest:Bashpt-query-digest /var/log/mysql-slow.log > /root/slow_report.txt
3. Read the Report:Bashless -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.

Summary Table of Commands

Goal	Command
Quick Top 10 (Simple)	mysqldumpslow -a -s at -t 10 /var/log/mysql-slow.log
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];

Related: 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?

Want a website builder that makes the web developing process a lot easier and faster? 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.