How to profile and debug MySQL queries for speed

Most MySQL applications need profiling for speed enhancements at one time or other. I’ve developed several web applications in the past wherein MySQL queries were a major bottleneck in the application performance. In the past however there were no easy tools to analyse SQL queries in a running application and we programmers had to depend on some makeshift solutions to debug SQL queries. Now, however there are some nice free tools which can help you profile MySQL application queries easily.

My favorite among them is Neor Profile SQL profiler. This is a desktop tool which allows you to easily profile your application SQL queries and also lets you query the same in a live application. The application is cross-platform and compatible with Microsoft Windows, Mac OS X and Linux. The Neor Profiler works by intercepting all the queries from the client and acts as a proxy server.

Changing MySQL port in your scripts

Default port of Neor Profile SQL is 4040. For intercepting SQL queries you must change the default host and port 3306 of MySQL connection as shown below.

PHP

$ link = mysql_connect('127.0.0.1:4040', 'YOUR_DATABASE', 'YOUR_PASSWORD');

If profiling WordPress then make the following changes.

Wordpress
File: wp-config.php
Code: define('DB_HOST', 'localhost:4040');

With other CMS or language it is the same. Just change the port to 4040.

Below is shown a typical WordPress home page load query profiling.

The interesting and powerful thing is that you can now also execute WordPress queries from within the Neor profiler itself and check the results. This is a really nice way to also debug SQL queries while the application is running.

Although there are a few MySQL profilers out there, I find Neor to be light-weight and non intrusive to work with.

2 thoughts on “How to profile and debug MySQL queries for speed

  1. Broken on Ubuntu:
    $ sqlprofiler
    QMetaObject::connectSlotsByName: No matching signal for on_tableSessions_selectionChanged()
    Segmentation fault (core dumped)

Leave a Reply

Your email address will not be published. Required fields are marked *