How to enable DB Profiler for Mysql Quries in magento2:
Magento2 by default providing option to enable the profiler for mysql quries in magento2. Which will more helpful to find out the mysql qurie execution. Based on the qurie execution we can also customize the mysql quries while in developmer mode itself.
Ex: Add following script from root/app/env.php file,
‘profiler’ =>[
‘class’ => ‘\Magento\Framework\DB\Profiler’,
‘enabled’ => true,
],
And add following script from index.php (To know more about below script, please refer Magento dev guide)
/** @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>”;
Once added the script, referesh the page. You will get the mysql query execution of the entire page like how many
query is executing on the page & how much time it is taking for the all query. According to the execution time, you can customize the query before go Production mode.
