{"id":9004,"date":"2026-04-27T17:25:39","date_gmt":"2026-04-28T00:25:39","guid":{"rendered":"https:\/\/www.ultimatewb.com\/blog\/?p=9004"},"modified":"2026-04-27T17:33:18","modified_gmt":"2026-04-28T00:33:18","slug":"the-developers-guide-to-identifying-and-optimizing-slow-mysql-queries","status":"publish","type":"post","link":"https:\/\/www.ultimatewb.com\/blog\/9004\/the-developers-guide-to-identifying-and-optimizing-slow-mysql-queries\/","title":{"rendered":"The Developer\u2019s Guide to Identifying and Optimizing Slow MySQL Queries"},"content":{"rendered":"\n
\n \n \"web-developer-optimizing-mysql-queries\"\n\n <\/picture>\n <\/figure>\n\n\n\n

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

If you\u2019re using UltimateWB<\/a><\/strong> for your backend, chances are you won\u2019t 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.<\/p>\n\n\n\n

Related: How to Lazy Load Google Adsense Ads for a Fast Website<\/a><\/p>\n\n\n\n

Why Avoiding Third-Party Plugins Makes Your Website Faster, Safer, and Easier to Manage<\/a><\/p>\n\n\n\n

Part 1: Prerequisites<\/strong><\/h2>\n\n\n\n

To follow this guide, you must have root access<\/strong> to your server via SSH.<\/p>\n\n\n\n

    \n
  1. Open your terminal<\/strong> and log in: ssh root@your-server-ip<\/code>.<\/li>\n\n\n\n
  2. Confirm your log path:<\/strong> In this example, the path is \/var\/log\/mysql-slow.log<\/code>.<\/li>\n\n\n\n
  3. Ensure logging is active:<\/strong> 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;\" <\/code>(Note: This sets the threshold to 2 seconds. Any query taking longer than this will be logged.)<\/em><\/li>\n<\/ol>\n\n\n\n

    Part 2: Extracting the Top 10 (Quick Method)<\/strong><\/h2>\n\n\n\n

    The mysqldumpslow<\/code> tool is pre-installed on almost all WHM servers. To get the exact queries<\/strong> (including the actual IDs and data), we use the -a<\/code><\/strong> (across) flag.<\/p>\n\n\n\n

    Command for Top 10 by Average Speed:<\/strong><\/p>\n\n\n\n

    Bash<\/p>\n\n\n\n

    mysqldumpslow -a -s at -t 10 \/var\/log\/mysql-slow.log\n<\/code><\/pre>\n\n\n\n
    Command for Top 10 by \u201cTotal Impact\u201d:<\/strong> This identifies queries that might be slightly faster but run so often they slow down the entire server.<\/p>\n\n\n\n
    Bash<\/p>\n\n\n\n
    mysqldumpslow -a -s t -t 10 \/var\/log\/mysql-slow.log\n<\/code><\/pre>\n\n\n\n
    Part 3: Advanced Analysis (Professional Method)<\/strong><\/h2>\n\n\n\n
    For a deep dive that provides specific optimization metrics, administrators often use the Percona Toolkit<\/strong>. This is the industry standard for managing high-traffic sites and massive databases.<\/p>\n\n\n\n
      \n
    1. Install the Toolkit:<\/strong>Bashyum install percona-toolkit -y<\/code><\/li>\n\n\n\n
    2. Generate the Digest:<\/strong>Bashpt-query-digest \/var\/log\/mysql-slow.log > \/root\/slow_report.txt<\/code><\/li>\n\n\n\n
    3. Read the Report:<\/strong>Bashless -S \/root\/slow_report.txt<\/code><\/li>\n<\/ol>\n\n\n\n
      What to look for in the report:<\/strong><\/p>\n\n\n\n