Magento SQL Logging – (Part II)

by ·1 Comment

In the last article we saw the different ways how one can analyse the SQL Queries running under the Magento’s hood. These are listed here again:

  1. To see all queries issued to the database
    1. Modify the core Magento code that deals with the database query generation
    2. Enable Magento Pdo MySql debugging
    3. Switch on MySQL logging
  2. To see only a specific query
    1. Modify the file with the collection under debugging

Last time we saw how one can log all SQL queries (option 1). In this article we will take a look at logging selective SQL queries.

Modify the file with the collection under debugging

Sometimes database logging is needed to check what query a particular code is executing. Using the previous 3 methods result in a large number of logs. Determining the query related to the code under examination will be a lengthy exercise. More targeted logging can be performed when the file creating the query or using the collection generating the database query is known. By adding a simple statement on the collection object one can print the statement being issued to the database. The line of code to log the query used by the collection object is:

  1. // $collection is the variable with the result set returned by the database
  2. Mage::log((string)$collection->getSelect());

Example

When a search is performed in Magento the results page contains a toolbar section. The toolbar shows how many products match the search, provides page navigation and also provides options to change the view.

The toolbar is created using the template file catalog/product/list/toolbar.phtml. We can now check the query that is used to determine how many products are being displayed. To get the query executed we modify the toolbar.phtml at line 34 and include our logging statement. Below is an extract of the modified toolbar.phtml

  1. <?php
  2. /**
  3.  * Product list toolbar
  4.  *
  5.  * @see Mage_Catalog_Block_Product_List_Toolbar
  6.  */
  7. ?>
  8. <?php Mage::log((string) $this->getCollection()->getSelect()); ?>
  9. <?php if($this->getCollection()->getSize()): ?>
  10. <div class="toolbar">

Note: For the change to take effect you might need to refresh the cache from the Magento admin site.

When the search page is refreshed the logging command gets executed and writes the SQL query in var/log/system.log. In our example the system.log entry for the toolbar.phtml is

2010-11-28T18:33:44+00:00 DEBUG (7): SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`,
`price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, 
IF(`price_index`.`tier_price`, LEAST(`price_index`.`min_price`, `price_index`.`tier_price`), 
`price_index`.`min_price`) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, 
`price_index`.`tier_price` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id 
AND cat_index.store_id='1' AND cat_index.visibility IN(2, 4) AND cat_index.category_id='10'
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id 
AND price_index.website_id = '1' AND price_index.customer_group_id = 0 ORDER BY 
`cat_index_position` asc, `cat_index`.`product_id` asc LIMIT 9

Conclusion

In Magento the SQL statements issued to the database can be logged using few different techniques. The techniques range from MySQL logging to specific query logging using Magento provided mechanisms.

As a final word of caution, whichever SQL logging technique used one must ensure that it is switched off on production servers as it will impact the performance of the system.

Articles:

References:

Magento: SQL Debugging Option – Programming Questions
Stackoverflow: How do I print all the queries in Magento
iFuel Interactive: Logging all SQL in Magento
RedLightBlinking: Debugging in Magento – how to find template paths, logging and display errors
MySQL: Server System Variables