Slow SQL Query on Magento 1.9 with MySqll (Percona) 5.7

Question

I have been working a lot with Magento 1.9. I was changing database to Percona 5.7 (from 5.6) but since then, I have a major issue with "initial" load (for example after database restart).

I have traced, that there is following query:

SELECT `main_table`.`entity_id`, main_table.`name`, main_table.`path`, `main_table`.`is_active`, `main_table`.`is_anchor`, `url_rewrite`.`request_path` FROM `catalog_category_flat_store_1` AS `main_table` LEFT JOIN `core_url_rewrite` AS `url_rewrite` ON url_rewrite.category_id=main_table.entity_id AND url_rewrite.is_system=1 AND url_rewrite.store_id = 1 AND url_rewrite.id_path LIKE 'category/%' WHERE (main_table.include_in_menu = '1') AND (main_table.is_active = '1') AND (main_table.path like '1/2/%') ORDER BY `main_table`.`position` ASC

From Explain on Percona 5.7 I have found following:

But when I run same query on same database (could be max 1 day old) but on Percona 5.6:

Query is definitely not using key, but why? Does somebody, please, have a suggestion?

EDIT:
I have setted up another shop on Percona 5.7, which has a lot less amount of rows in "core_url_rewrite". I can see, that keys are being used now (unable to paste screenshot)

So it looks like the problem is on the Percona side, perhaps the size of the table goes outside "buffer".

Answer 1

OK, I have found a workaround. It is problematic only in the table, which has more than 2mil records.

We are searching for a categories, but inside this table there are a lot of rows regarding products. When I changed Magento Core file to add:

  ' AND url_rewrite.category_id is not null' 

which helps database to pre-filter results. After that, the keys are used. If I can say that it is resolution or workaround, I don't know, but it is working.

Make a local copy of app/Core/Mage/Catalog/Helper/Category/Url/Rewrite.php and find:

public function joinTableToSelect(Varien_Db_Select $select, $storeId)
{
    $select->joinLeft(
        array('url_rewrite' => $this->_resource->getTableName('core/url_rewrite')),
            'url_rewrite.category_id=main_table.entity_id AND url_rewrite.is_system=1 AND ' .
             $this->_connection->quoteInto('url_rewrite.store_id = ? AND ', (int)$storeId) .
            $this->_connection->prepareSqlCondition('url_rewrite.id_path', array('like' => 'category/%')),
        array('request_path' => 'url_rewrite.request_path'));
    return $this;
}

and change to this:

public function joinTableToSelect(Varien_Db_Select $select, $storeId)
{
    $select->joinLeft(
        array('url_rewrite' => $this->_resource->getTableName('core/url_rewrite')),
            'url_rewrite.category_id=main_table.entity_id AND url_rewrite.is_system=1 AND ' .
             $this->_connection->quoteInto('url_rewrite.store_id = ? AND ', (int)$storeId) .
            $this->_connection->prepareSqlCondition('url_rewrite.category_id', array('notnull' => 1)) .
            $this->_connection->prepareSqlCondition(' AND url_rewrite.id_path', array('like' => 'category/%')),
        array('request_path' => 'url_rewrite.request_path'));
    return $this;
}

This will add another filter to query.

Answer 2

You can replace

core_url_rewrite.id_path LIKE 'category/%'

to

core_url_rewrite.id_path REGEXP '^category/'

Magento + MySQL 5.7 = Slow Queries