Stocks stops the whole page

[egz]

Hello,
I would like to ask for help where the error might be.
The whole shop works very well and fast, there are absolutely no performance issues except for stocks.
When I click STOCK everything stops it even blocks front page for user. This only happens here, otherwise everything runs incredibly smoothly.
When I call the url directly:
api/stocks/?_token=...........&order=product&page_size=30&page_index=1
i have a message like the one below:

The query shown on the error page in phpmyadmin executes in 0.2s
The server is very efficient and there is no problem with it.

I would be grateful if you could advise me where to look for the cause.

PrestaShop: 1.7.8.7
Server: LiteSpeed
PHP: 7.4.32
Memory limit: 1024M
Execution time: 300
All files are original
Template: default

SELECT SQL_CALC_FOUND_ROWS
p.id_product AS product_id,
COALESCE(pa.id_product_attribute, 0) AS combination_id,
IF(COALESCE(p.reference, "") = "", "N/A", p.reference) AS product_reference,
IF(COALESCE(pa.reference, "") = "", "N/A", pa.reference) AS combination_reference,
pl.name AS product_name,
p.id_supplier AS supplier_id,
COALESCE(s.name, "N/A") AS supplier_name,
COALESCE(ic.id_image, 0) AS product_cover_id,
p.active,
sa.quantity AS product_available_quantity,
sa.physical_quantity AS product_physical_quantity,
sa.reserved_quantity AS product_reserved_quantity,
IF(COALESCE(pa.id_product_attribute, 0) > 0, COALESCE(pas.low_stock_threshold, "N/A"),
COALESCE(ps.low_stock_threshold,
"N/A")) AS product_low_stock_threshold,
IF(COALESCE(pa.id_product_attribute, 0) > 0, IF(sa.quantity <= pas.low_stock_threshold, 1, 0),
IF(sa.quantity <= ps.low_stock_threshold, 1, 0)) AS product_low_stock_alert,
(SELECT GROUP_CONCAT(
DISTINCT CONCAT(agl.name, " - ", al.name)
SEPARATOR ", "
)
FROM ps_product_attribute pa2
JOIN ps_product_attribute_combination pac ON (
pac.id_product_attribute = pa2.id_product_attribute
)
JOIN ps_attribute a ON (
a.id_attribute = pac.id_attribute
)
JOIN ps_attribute_lang al ON (
a.id_attribute = al.id_attribute
AND al.id_lang = :language_id
)
JOIN ps_attribute_group ag ON (
ag.id_attribute_group = a.id_attribute_group
)
JOIN ps_attribute_group_lang agl ON (
ag.id_attribute_group = agl.id_attribute_group
AND agl.id_lang = :language_id
)
WHERE pa2.id_product=p.id_product AND pa2.id_product_attribute=pa.id_product_attribute)
AS combination_name,
(SELECT GROUP_CONCAT(
DISTINCT CONCAT(agl.name)
SEPARATOR ", "
)
FROM ps_product_attribute pa2
JOIN ps_product_attribute_combination pac ON (
pac.id_product_attribute = pa2.id_product_attribute
)
JOIN ps_attribute a ON (
a.id_attribute = pac.id_attribute
)
JOIN ps_attribute_group ag ON (
ag.id_attribute_group = a.id_attribute_group
)
JOIN ps_attribute_group_lang agl ON (
ag.id_attribute_group = agl.id_attribute_group
AND agl.id_lang = :language_id
)
WHERE pa2.id_product=p.id_product AND pa2.id_product_attribute=pa.id_product_attribute)
AS attribute_name
FROM ps_product p
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product AND pl.id_lang = :language_id)
INNER JOIN ps_product_shop ps ON (p.id_product = ps.id_product AND ps.id_shop = :shop_id)
LEFT JOIN ps_stock_available sa
ON (p.id_product = sa.id_product AND sa.id_shop = :stock_shop_id AND sa.id_shop_group = :stock_group_id AND
sa.id_product_attribute = COALESCE(pa.id_product_attribute, 0))
LEFT JOIN ps_image ic ON (p.id_product = ic.id_product AND ic.cover = 1)
LEFT JOIN ps_image_shop ims
ON (p.id_product = ims.id_product AND ic.id_image = ims.id_image AND ims.id_shop = :shop_id AND ims.cover = 1)
LEFT JOIN ps_supplier s ON (p.id_supplier = s.id_supplier)
LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps_product_attribute_shop pas
ON (pas.id_product = pa.id_product AND pas.id_product_attribute = pa.id_product_attribute AND
pas.id_shop = :shop_id)
WHERE
p.state = :state

GROUP BY p.id_product, pa.id_product_attribute
HAVING 1
ORDER BY product_id, combination_id

 

Edited December 7, 2022 by egz (see edit history)