MysqlQueryProfiler

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

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);

2. 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>';
}