Fulltext search index: slow query in resetSearchResults()

[arhu32]

In Magento/CatalogSearch/Model/Resource/Fulltext.php, function resetSearchResults() there are some queries that set the whole column "is_processed" in the table "catalogsearch_query" to 0, e.g.:

$adapter->update($this->getTable('catalogsearch_query'),
    array('is_processed' => 0));

This query is very slow because it possibly needs to update lots of rows every time resetSearchResults() is called. On our server (mage181, multi-store setup, no SSD) with ~40.000 rows in the table, the query took 0.5 seconds and was called regularly during product updates etc.

Fix:

1. Only update rows that are not already 0:

   $adapter->update($this->getTable('catalogsearch/search_query'),
       array('is_processed' => 0), array('is_processed != 0'));

2. Add an index to the "is_processed" column in the "catalogsearch_query" table.

This will speed up the query considerably. AFAIK this issue affects all Magento versions, including 1.8.1.0.

[verklov]

@arhu32, thank you for reporting this to us! We created a ticket in the backlog and will get back to you once the team investigates on this issue.

[bachsh]

hey @verklov , was this issue solved in CE 1.9?

[verklov]

Hi @bachsh, I don't think it is solved in CE1.9 already. In any case, once we have a solution for M2, we will have it requested to be ported to M1 by the M1 team.

[vpelipenko]

Internal ticket: MAGETWO-24366

[kokoc]

@arhu32 The fix is now available in 0.74.0-beta10. Thank you very much for your contribution and continued support to Magento 2! Could you please verify the issue?