Slow products category query optimization

[WojtekA]

Hello guys 😀, for several days, I have been struggling with the speed of product listing in categories. I'm using PS version 1.7.8.6, and I've noticed that the first query that retrieves the sorted product IDs is problematic.

An example query takes several dozen seconds:
It looks like this:

SELECT SQL_NO_CACHE p.id_product
FROM (
  SELECT p.id_product, p.id_manufacturer, SUM(sa.quantity) AS quantity, p.condition, p.weight, p.price, psales.quantity AS sales, cp.position
  FROM ps_product p
  LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
  LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute)
  LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute
    AND sa.id_shop = 4 AND sa.id_shop_group = 0)
  LEFT JOIN ps_product_sale psales ON (psales.id_product = p.id_product)
  INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product)
  INNER JOIN ps_category c ON (cp.id_category = c.id_category AND c.active=1)
  LEFT JOIN ps_category_group cg ON (cg.id_category = c.id_category)
  INNER JOIN ps_product_shop ps ON (p.id_product = ps.id_product AND ps.id_shop = 4 AND ps.active = TRUE)
  INNER JOIN ps_category c_1 ON (cp.id_category = c_1.id_category AND c_1.active=1)
  WHERE p.visibility IN ('both', 'catalog') AND cg.id_group='1' AND c.nleft>=75 AND c.nright<=124 AND ps.id_shop='4'
  GROUP BY p.id_product) p
INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product)
GROUP BY p.id_product
ORDER BY p.position ASC, p.id_product DESC
LIMIT 0, 80

From what I have debugged, the same subquery executes in a fraction of a second, so there is some problem in this surrounding query, which (according to EXPLAIN) is done by "filesort".

I tried to do something about it, but the only thing I managed to do on the queries themselves, without completely rebuilding them, is to add a condition surrounding the query:

INNER JOIN ps_category c ON (cp.id_category = c.id_category AND c.active=1 AND c.nleft>=75 AND c.nright<=124)

Then such a query is performed much faster.

 

The only question is how to do this in code?

I tried to add in the initSearch() method in the ps_facetedsearch/src/Product/Search.php file:
 

// $this->addFilter('nleft', [$parent->nleft], '>=');
// $this->addFilter('nright', [$parent->nright], '<=');
$this->getSearchAdapter()->addFilter('nleft', [$parent->nleft], '>=');
$this->getSearchAdapter()->addFilter('nright', [$parent->nright], '<=');

but somehow it doesn't want to work 😕

I'm just starting my adventure with Presta and I don't know this system well.
Maybe someone have an idea where to add some code to change this SQL query, or maybe there is another solution to optimize category speed?

[Knowband Plugins]

Hi,

To improve product listing speed in PrestaShop by focusing on indexing, follow these steps:

1. Identify Key Columns: Use the EXPLAIN SQL command to analyze your query and identify columns frequently involved in JOIN, WHERE, and ORDER BY clauses.

2. Create Indexes: Based on this analysis, create indexes on these key columns.
For example:
ALTER TABLE ps_category ADD INDEX (id_category, nleft, nright);
ALTER TABLE ps_product ADD INDEX (id_product);

This approach should enhance query performance by optimizing database search efficiency.

[WojtekA]

Hello, adding indexes didn't change anything, speed is the same.

I have checked EXPLAIN before (results below):

I think that the problem is, due to using “temporary” and "filesort" 😕

The subquery (1) by itself executes very quick, in a few milliseconds:

Subquery (1):

  SELECT p.id_product, p.id_manufacturer, SUM(sa.quantity) AS quantity, p.condition, p.weight, p.price, psales.quantity AS sales, cp.position
  FROM ps_product p
  LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
  LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute)
  LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute
    AND sa.id_shop = 4 AND sa.id_shop_group = 0)
  LEFT JOIN ps_product_sale psales ON (psales.id_product = p.id_product)
  INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product)
  INNER JOIN ps_category c ON (cp.id_category = c.id_category AND c.active=1)
  LEFT JOIN ps_category_group cg ON (cg.id_category = c.id_category)
  INNER JOIN ps_product_shop ps ON (p.id_product = ps.id_product AND ps.id_shop = 4 AND ps.active = TRUE)
  INNER JOIN ps_category c_1 ON (cp.id_category = c_1.id_category AND c_1.active=1)
  WHERE p.visibility IN ('both', 'catalog') AND cg.id_group='1' AND c.nleft>=75 AND c.nright<=124 AND ps.id_shop='4'
  GROUP BY p.id_product

 

But when added this (1) query to the big query, it probably stores this in temporary table or file, and use in outer query, that's why it takes several seconds then.

Outer query:
 

EXPLAIN SELECT SQL_NO_CACHE p.id_product
FROM (
  {SUBQUERY (1) HERE}
) p
INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product)
GROUP BY p.id_product
ORDER BY p.position ASC, p.id_product DESC
LIMIT 0, 80

I'm not sure if anything can be done here, because I think that is how PrestaShop core works, to make subquery first, to get product IDS, and then use this IDs in final query with sorting, filtering, etc.

 

[fjlozano]

Hello. I have the same problem. Did you find any way to solve it that you could share with us?

[WojtekA]

17 hours ago, fjlozano said:

Hello. I have the same problem. Did you find any way to solve it that you could share with us?

Hello, actually, I made a simple change in one file. In ProductListingFrontController.php changed the last line in below code.

In my situation there was some strange provider used FacetedSearch/SearchProvider (from ps_facetedsearch), which cause additional queries as described above.

By changing the $provider to null, the system will use default SearchProvider, which use only one query, without this subquery.

I hope it helps in your situation too
 

    protected function getProductSearchVariables()
    {
        /*
         * To render the page we need to find something (a ProductSearchProviderInterface)
         * that knows how to query products.
         */

        // the search provider will need a context (language, shop...) to do its job
        $context = $this->getProductSearchContext();

        // the controller generates the query...
        $query = $this->getProductSearchQuery();

        // ...modules decide if they can handle it (first one that can is used)
        $provider = null; // $this->getProductSearchProviderFromModules($query);

The only difference with this provider, and the previous provider is that sometimes the order of products in the category is slightly different, but for about 20 products, only 2 are switched, so the difference is negligible with such an increase in speed