SQL stands for Structured Query language which can be effectively used to store, manipulate, and retrieve data in database. In Magento, it provides a great deal of insight into the behind-the-scenes functionality to debug slow-running queries and other Model-based performance problems. Today, let’s go insight into how to enable logging SQL on Magento 1.x.
To begin with, please open file folder_code/lib/Varien/Db/Adapter/Pdo/Mysql.php in file editor. At line 106:
/**
* Write SQL debug data to file
*
* @var bool
*/
protected $_debug = false;
/**
* Minimum query duration time to be logged
*
* @var float
*/
protected $_logQueryTime = 0.05;
/**
* Log all queries (ignored minimum query duration time)
*
* @var bool
*/
protected $_logAllQueries = false;
/**
* Add to log call stack data (backtrace)
*
* @var bool
*/
protected $_logCallStack = false;
/**
* Path to SQL debug data log
*
* @var string
*/
protected $_debugFile = 'var/debug/pdo_mysql.log';
protected $_debug = false;
To enable logging SQL change value to “true”.
protected $_logQueryTime = 0.05;
This set minimum query duration time to be logged. To find slow query change this value to 1.0 seconds.
protected $_logAllQueries = false;Change this value to “true” to log everything.
protected $_logCallStack = false;This logs a backtrace of methods executed.
protected $_debugFile = 'var/debug/pdo_mysql.log';Logs file will be created at folder var/debug/pdo_mysql.log
The logs executed will be like this:
## 2018-05-20 15:39:03
## 6752 ## QUERY
SQL: SELECT `core_url_rewrite`.* FROM `core_url_rewrite` WHERE (request_path IN (:path0, :path1)) AND (store_id IN(0, 1))
BIND: array (
':path0' => 'customer/account/index/',
':path1' => 'customer/account/index',
)
AFF: 0
TIME: 0.0004
TRACE: #1 Magento_Db_Adapter_Pdo_Mysql[Varien_Db_Adapter_Pdo_Mysql]#000000006b4f0dd900000000447f845e#->_debugStat(2, 'SELECT `core_url...', array(':path0' => 'customer/account...', ':path1' => 'customer/account...'), &Varien_Db_Statement_Pdo_Mysql#000000006b4f0de600000000447f845e#) called at [lib\Varien\Db\Adapter\Pdo\Mysql.php:509]
#2 Magento_Db_Adapter_Pdo_Mysql[Varien_Db_Adapter_Pdo_Mysql]#000000006b4f0dd900000000447f845e#->query('SELECT `core_url...', array(':path0' => 'customer/account...', ':path1' => 'customer/account...')) called at [lib\Zend\Db\Adapter\Abstract.php:737]
#3 Magento_Db_Adapter_Pdo_Mysql[Zend_Db_Adapter_Abstract]#000000006b4f0dd900000000447f845e#->fetchAll(&Varien_Db_Select#000000006b4f0de700000000447f845e#, array('path0' => 'customer/account...', 'path1' => 'customer/account...')) called at [app\code\core\Mage\Core\Model\Resource\Url\Rewrite.php:151]
#4 Mage_Core_Model_Resource_Url_Rewrite#000000006b4f0dfb00000000447f845e#->loadByRequestPath(&Mage_Core_Model_Url_Rewrite#000000006b4f0dd700000000447f845e#, array('customer/account...', 'customer/account...')) called at [app\code\core\Mage\Core\Model\Url\Rewrite.php:100]
#5 Mage_Core_Model_Url_Rewrite#000000006b4f0dd700000000447f845e#->loadByRequestPath(array('customer/account...', 'customer/account...')) called at [app\code\core\Mage\Core\Model\Url\Rewrite\Request.php:139]
#6 Mage_Core_Model_Url_Rewrite_Request#000000006b4f0dd100000000447f845e#->_rewriteDb() called at [app\code\core\Mage\Core\Model\Url\Rewrite\Request.php:116]
#7 Mage_Core_Model_Url_Rewrite_Request#000000006b4f0dd100000000447f845e#->rewrite() called at [app\code\core\Mage\Core\Controller\Varien\Front.php:165]
#8 Mage_Core_Controller_Varien_Front#000000006b4f0dd300000000447f845e#->dispatch() called at [app\code\core\Mage\Core\Model\App.php:365]
#9 Mage_Core_Model_App#000000006b4f0dcb00000000447f845e#->run(array('scope_code' => '', 'scope_type' => 'store', 'options' => array())) called at [app\Mage.php:683]
#10 Mage::run('', 'store') called at [index.php:83]We hope that the article is helpful for you to enable SQL log in minutes. Feel free to take a tour around and explore dozens of well-coded Magento extensions here.
