php - Prestashop 1.7.6.8 getproducts function slow ← (PHP)

I'm facing a weird issue with PS 1.7.6.8 with my custom module.

Let me explain the whole thing before :

• I built a custom module from an existing module for searching car parts by an unique ID called Ktype. This is working perfectly.
• I use the Prestashop Search controller to search products, then return them and build a product listing using Product Assembler and Product Listing Assembler

The search controller gets ids from Make, Model, Year and Device from my module, everything is stored in a database table including : Product ID's, ID Make, ID Model, ID Year, ID Device.

When the make, model, year and device is selected, i build an array from the id_product and store it into the variable : pSQL ($id_products)

Then, to show products, i use this function :

    public function getProducts($where, $id_lang, $p, $n, $order_by = null, $order_way = null, $get_total = false, $active = true, $random = false, $random_number_products = 1, $check_access = true, Context $context = null)
{
    # validate module
    unset($check_access);
    if (!$context) {
        $context = Context::getContext();
    }
    $front = true;
    if (!in_array($context->controller->controller_type, array('front', 'modulefront'))) {
        $front = false;
    }
    if ($p < 1) {
        $p = 1;
    }
    $id_supplier = (int) Tools::getValue('id_supplier');

    
    $sql = 'SELECT DISTINCT p.id_product, p.*, product_shop.*, pl.`description`, pl.`description_short`, pl.`available_now`,
            pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, m.`name` AS manufacturer_name, cl.`name` AS category_default, product_shop.price AS orderprice
        FROM `' . _DB_PREFIX_ . 'category_product` cp
        INNER JOIN `' . _DB_PREFIX_ . 'product` p
            ON p.`id_product` = cp.`id_product`
        ' . Shop::addSqlAssociation('product', 'p') . '
        LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa
        ON (p.`id_product` = pa.`id_product`)
        ' . Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1') . '
        LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl
            ON (product_shop.`id_category_default` = cl.`id_category`
            AND cl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl') . ')
        LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl
            ON (p.`id_product` = pl.`id_product`
            AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ')
        LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m
            ON m.`id_manufacturer` = p.`id_manufacturer`
        ' . $where . '
            '
            . (Tools::getValue('filter_cat') ? ' AND cp.id_category='. (int) Tools::getValue('filter_cat') : '');
            
        if (Tools::getValue('order') == 'product.position.asc') {
            $orderby = 'cp.position ASC';
        }
        if (Tools::getValue('order') == 'product.name.asc') {
            $orderby = 'pl.name ASC';
        }
        if (Tools::getValue('order') == 'product.name.desc') {
            $orderby = 'pl.name DESC';
        }
        if (Tools::getValue('order') == 'product.price.asc') {
            $orderby = 'product_shop.price ASC';
        }
        if (Tools::getValue('order') == 'product.price.desc') {
            $orderby = 'product_shop.price DESC';
        }

    $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
            foreach ($result AS $row) {
                if ($row2 = Product::getProductProperties($id_lang, $row)) {
                    $products[] = $row2;
            }
            }           
    return $products;
}

$where variable = WHERE p.id_product IN (pSQL $id_products)

The weird thing is that, when this SQL query is called :

1. I can see through phpMyAdmin that this query takes less than 1 second to complete
2. Then, lot of other queries, probably coming from the product controller, are executed next.

In my pSQL($id_products) variable, i can have like 4000 product ids comma separated, and i can see the product controller queries are executed for each product_id, and for each parameter that the front controller need (one query for image, one for quantity, one for category, one for add_date, etc ...) leading to a huge number of queries (average is about 150.000 queries for a single search).

I'm using a VPS with 64 GB RAM, 16 Cores Intel I7, and all indexes are used perfectly.

This query takes approx 15 seconds to finish, which is unusable for a webshop.

I already asked for core developers if they have seen such issue before but i never had any answer about PS support.

Is there any way to avoid those multiple subqueries ?

Does it come from the WHERE IN clause in my SQL query ? As each value is comma separated, maybe the controller understand it as a single product, leading to multiple subqueries for all.

If i use a JOIN instead of WHERE IN clause, may it work ? Or i need to keep the WHERE IN clause for the product controller to work ?.

If someone can help me on this, this is driving me crazy.

Thanks for your help !

Solution:

For each row you call Product::getProductProperties, which is a heavy functionality.

You should estimate if you need data from that method, and if it is not necessary override getProducts and avoid Product::getProductProperties

On the other hand, 4000 products in a category I think is too much, you should subcategorize.