HummingbirdUK main logo

HummingbirdUK

Coding solutions to business problems

About us

We use code to create solutions to business challenges, bottle-necks and headaches.

If you think your business has a problem that can be solved through code, we are happy to chat things through without any obligation.

Get in touch

Magento admin panel category page timeout

Home / Blog / Magento admin panel category page timeout

Written by Giles Bennett

A pesky issue that had been affecting a client was, finally, solved. When viewing a category - no matter how large or small, in the admin panel, the ajax request would time out. It's fair to say that we tried pretty much everything - the system was running Nginx and PHP7.0, so time was spent looking at the configuration of Nginx itself, to make sure that the ajax request parameters were being fed through properly. Further time was spent checking that the setup of both PHP and MySQL were making the most of the resources available to them, but to no avail.

The issue didn't really manifest itself on the front of the site, but it was particularly irritating when trying to administer the site's catalogue. So the next step was to start looking at the SQL queries being run when the request went in - sure enough, MySQL was spending a very long time on a query that looked something like this :


SELECT `e`.*, `core_url_rewrite`.`request_path` FROM `catalog_category_entity` AS `e`
 LEFT JOIN `co...

Further tracking down lead to this file :


public/app/code/core/Mage/Catalog/Helper/Category/Url/Rewrite.php

and within it to the following function :


    public function joinTableToEavCollection(Mage_Eav_Model_Entity_Collection_Abstract $collection, $storeId)
    {
        $collection->joinTable(
            'core/url_rewrite',
            'category_id=entity_id',
            array('request_path'),
            "{{table}}.is_system=1 AND " .
                "{{table}}.store_id='{$storeId}' AND " .
                "{{table}}.id_path LIKE 'category/%'",
            'left'
        );
        return $this;
    }

It transpired that within the 550,000 rows in the core_url_rewrite table a lot of the rows related to products, and not to categories, so to speed things up it would be better to filter the query so that it only looked at categories by adding in a line as follows :


            "{{table}}.is_system=1 AND " .
                "{{table}}.store_id='{$storeId}' AND " .
                "{{table}}.category_id is not null  AND " .
                "{{table}}.id_path LIKE 'category/%'",
            'left'

Copying this to the local file structure, so that it survived any changes to the core code, resulted in the issue being fixed and things returning to normal.

Author : Giles Bennett

About the author

Giles Bennett built his first website in 1996, and is old enough to miss Netscape Navigator. Initially a lawyer, he jumped ship to IT in 2008, and after 5 years as a freelancer, he founded HummingbirdUK in 2013. He can be reached by email at giles@hummingbirduk.com.