/MysqlQueryProfiler

A tool to profile mysql queries in php env.

Primary LanguagePHPMIT LicenseMIT

MysqlQueryProfiler

This tool helps you to quickly profile a mysql query in a PHP 7.4+ environnement. You can also compare 2 queries.

116562521-85d11400-a903-11eb-85e6-c4dbe0ee42f9

This image shows the results when comparing 2 ip search queries ((A) BETWEEN + index vs (B) INTERSECTS + SPACIAL index):

  • left: query cost (SHOW STATUS...)
  • right: query plan (SHOW PROFILE...)
  • bottom: optimizer information (EXPLAIN...)

This is a standalone one file php script. Not dependency (vanilla js, css and standard php modules only).

Why use it?

It helps you to:

  • find mysql configuration issues
  • improve your indexes
  • improve your queries
  • spot mysql limitations

Features

  • Display the following reports:
    • query cost (SHOW STATUS...)
    • query plan (SHOW PROFILE...)
    • optimizer information (EXPLAIN...)
  • Compare 2 queries
  • Highlight the better values
  • Link to the mysql doc for status metric
  • Dark and light modes (prefers-color-scheme)
  • IP filtering

Usage

Use it only in DEV and control who can access it!

  1. Copy the file in a secure location (with .htaccess, etc.)
  2. Create a mysql user with profiling privileges.
  3. Configure the tool (user, password, ip allow list, etc.)

Usage within Docker

The following will create a PHP 7.4 container with the mysql query profiler and also a mariadb 10.4 container

docker-compose up -d

Open http://localhost/mysql_query_profiler.php in your web browser

To stop, run docker compose down

Integration

You may want to profile the queries generated by your application by clicking on a link from your web pages.

  1. In your main configuration file, add a constant that will allow you to turn on/off the query displaying. For example:
define('MQP_PROFILE_QUERIES', true);
  1. Copy-paste-adapt this code in a method where all your queries go through:
if (MQP_PROFILE_QUERIES) {
  echo '<div style="border:1px solid #ff9966;padding:5px;margin:5px">';
  echo '<a href="/mysql_query_profiler.php?query=' . urlencode($query) . '" target="mqp">';
  echo htmlspecialchars($query);
  echo '</a>';
  echo '</div>';
}