Get and show the collection SQL query on Magento

I’ve been “suffering” out of memory problems on one of our clients magento installations. The version is the 1.4.0.1, and it seems that the queries are not as efficient as they are on Magento 1.5 and above.

So I needed to find out what were exactly the SQL queries that produced that out of memory error. The most difficult thing is to find where the collection query is done. Maybe there is a base collection function where we can execute the query, but if not, the only way at this moment is to find the prepareCollection() function loaded:

1. Find where your collection is get. In my case, I needed to find the last 5 orders query that is shown on the Dashboard. I found it in app/code/core/Mage/Adminhtml/Block/Widget/Grid.php, in the _prepareCollection() function.

2. At the end of that function there is a «$this->getCollection()», this one is the variable that has the collection. Now, we only need to add ->getSelect()->__toString in order to show the query:

Zend_Debug::dump($this->getCollection()->getSelect()->__toString());

3. Now we only need to update the page where the query is done and we’ll see the SQL query

I usually use Zend_Debug::dump() function to show things on screen.