Optimize Product Collection Filter

Question

I have a Model in my code that creates a custom product collection based on a user search term. That search term should search products and filter it by sku or name... The issue is that it takes too much time to retrieve the data, maybe from 4 to 9 seconds... my catalog has 8000 skus. I would like to optimize this filter. At the same time I have flat catalog enabled as well as categories. And I'm also have Elasticsearch enabled (My current installation has Magento 2.3.5p1). Here is my code:

public function getSearchResult($queryText)
{
    try {
        /** @var \Magento\Catalog\Model\ResourceModel\Product\Collection $productCollection */
        $productCollection = $this->layerResolver->get()->getProductCollection();

        $queryLike = $this->_getQueryPattern($queryText);

        $productCollection
            ->setVisibility([
                Visibility::VISIBILITY_BOTH,
                Visibility::VISIBILITY_IN_SEARCH,
                Visibility::VISIBILITY_IN_CATALOG
            ])
            ->addFieldToFilter('status', Status::STATUS_ENABLED)
            ->addAttributeToFilter('type_id', ['neq' => ProductType::TYPE_BUNDLE])
            ->addAttributeToFilter(
                [
                    ['attribute' => 'sku', 'like' => $queryLike],
                    ['attribute' => 'name', 'like' => $queryLike]
                ]
            );
        $productCollection->getSelect()->limit($this->searchModel->getMaxResShow());

        $this->stockFilter->addInStockFilterToCollection($productCollection);

        $productCollection = $this->searchModel->getResData($productCollection);

        $query_test = $productCollection->getSelect()->__toString();

        $this->_logger->debug(print_r($query_test,true));

        if (!empty($productCollection)) {
            $data = $productCollection->toArray([
                'name',
                'sku',
                'entity_id',
                'type_id',
                'product_hide_price',
                'product_hide_html',
                'product_thumbnail',
                'product_url',
                'popup',
                'product_price',
                'product_price_amount',
                'product_price_exc_tax_html',
                'product_price_exc_tax',
                'inner',
                'master',
            ]);
            return $data;
        }
        return false;
    } catch (\Magento\Framework\Exception\NoSuchEntityException $exception) {
        return false;
    }
}

/**
 * @param string $queryText
 * @return string
 */
private function _getQueryPattern($queryText)
{
    $queryText = preg_replace('/\s+/', '%', $queryText);
    $queryLike = '%' . $queryText . '%';
    return $queryLike;
}

Now, the raw SQL generated by this filter... on $query_test.. is the following: https://pastebin.pl/view/raw/ae39c71f . Please check!.

My question is... is there a way to optimize this?. Or, is there a way to use Elasticsearch to improve this?. I mean... this is taking too much time to retrieve the data... I mean, it takes from 4 to 9 seconds to retrieve the filter. What I think it could be optimized is the e.entity_id IN part... but based on magento 2 practices that I think I'm following what should I do to improve it?.

Thanks in advance for any kind of help and thanks for reading.

Answer 1

If you don't need the layer resolver for anything special, you could inject the \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory in your constructor and store it in a $productCollection class property, then you can replace the

$productCollection = $this->layerResolver->get()->getProductCollection();

with

$productCollection = $this->productCollection->create()

This will exclude all those IDs you carry with you because of the layer resolver.