Magento admin panel category page timeout

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.