Reading list Switch to dark mode

    How to debug slow MYSQL queries in Magento 2

    Updated 22 February 2024

    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

    Searching for an experienced
    Magento 2 Company ?
    Find out More
    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


    Lastly, there is an amazing tool named Quick Development Toolbar (GitHub)

    This repository will add a floating toolbar on top of your Magento 2 pages.


    When you go to the Queries tab you will see that all queries for that page are listed here with query time and other details


    Hope this helps you in debugging and writing better code.

    . . .

    Leave a Comment

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

    Be the first to comment.

    Back to Top

    Message Sent!

    If you have more details or questions, you can reply to the received confirmation email.

    Back to Home