How to debug slow MYSQL queries in Magento 2

This article talks about a couple of ways in which we can debug MYSQL queries(slow queries, waiting for a long process) for Magento 2 and find the adaptive solution.

First, Magento by default provides us with its inbuilt mysql query logger functionality.

To enable the same we can run the following command

php bin/magento dev:query-log:enable

This command will write database activity logs to the <magento_root>/var/debug/db.log file

To disable the MySQL query logger use the following command

php bin/magento dev:query-log:disable

We can also log limited data, for that please check php bin/magento dev:query-log:enable –help command to know further options.

Second, We can use the MYSQL Command Line tool to debug slow queries in order to do so please follow the below

Log into your MYSQL by running the following command
mysql -u root -p
Select the database
use magento_database;
Now, you need to enable the slow query log
SET GLOBAL slow_query_log = 'ON';
You can also set query running time threshold in seconds
SET GLOBAL long_query_time = X;
Now, you can check if the above settings are properly done or not, run the following command
show variables like '%slow%’;

Here you can also check your log file path

Third, is Magento 2 DB Profiler

The Magento 2 database profiler displays all queries implemented on a page, including the time for each query and what parameters were applied.

We will be outputting the following options for our example here

Total time (displays the total amount of time to run all queries on the page)
SQL (displays all SQL queries; the row header displays the count of queries)
Query Params (displays the parameters for each SQL query)

In order to enable this you need to modify /app/etc/env.php with the following code under db->connection->default.

'profiler' => [
    'class' => '\Magento\Framework\DB\Profiler',
    'enabled' => true,
],

final file content

'db' => [
    'table_prefix' => '',
    'connection' => [
        'default' => [
            'host' => 'localhost',
            'dbname' => 'magento',
            'username' => 'root',
            'password' => 'password',
            'model' => 'mysql4',
            'engine' => 'innodb',
            'initStatements' => 'SET NAMES utf8;',
            'active' => '1',
            'driver_options' => [
                1014 => false
            ],
            'profiler' => [
                'class' => '\Magento\Framework\DB\Profiler',
                'enabled' => true,
            ]
        ]
    ]
],

Now, you need to configure where to see the output and for that follow below

open /pub/index.php and add following code after $bootstrap->run($app); line

/** @var \Magento\Framework\App\ResourceConnection $res */
$res = \Magento\Framework\App\ObjectManager::getInstance()->get('Magento\Framework\App\ResourceConnection');
/** @var Magento\Framework\DB\Profiler $profiler */
$profiler = $res->getConnection('read')->getProfiler();
echo "<table cellpadding='0' cellspacing='0' border='1'>";
echo "<tr>";
echo "<th>Time <br/>[Total Time: ".$profiler->getTotalElapsedSecs()." secs]</th>";
echo "<th>SQL [Total: ".$profiler->getTotalNumQueries()." queries]</th>";
echo "<th>Query Params</th>";
echo "</tr>";
foreach ($profiler->getQueryProfiles() as $query) {
    /** @var Zend_Db_Profiler_Query $query*/
    echo '<tr>';
    echo '<td>', number_format(1000 * $query->getElapsedSecs(), 2), 'ms', '</td>';
    echo '<td>', $query->getQuery(), '</td>';
    echo '<td>', json_encode($query->getQueryParams()), '</td>';
    echo '</tr>';
}
echo "</table>";

Result will be in bottom of the page