Brand page

[Sickboards]

Hello 

In the backoffice catalog=>brands this statement is executed.

Only this SQL statement is too heavy for large shops causing the server to timeout.

SELECT SQL_CALC_FOUND_ROWS a.* , COUNT(`id_product`) AS `products`, ( SELECT COUNT(ad.`id_manufacturer`) as `addresses` FROM `ps_address` ad WHERE ad.`id_manufacturer` = a.`id_manufacturer` AND ad.`deleted` = 0 GROUP BY ad.`id_manufacturer`) as `addresses` FROM `ps_manufacturer` a LEFT JOIN `ps_product` p ON (a.`id_manufacturer` = p.`id_manufacturer`) WHERE 1 AND EXISTS ( SELECT 1 FROM `ps_manufacturer_shop` sa WHERE a.`id_manufacturer` = sa.`id_manufacturer` AND sa.id_shop IN (1) ) GROUP BY a.`id_manufacturer` ORDER BY name ASC LIMIT 0, 50;

(Even straight in phpMyAdmin)

Where is this defined and how can I rewrite it so it is much faster?

[Nickz]

1 hour ago, Sickboards said:

Only this SQL statement is too heavy for large shops causing the server to timeout.

How much power has your server?
PHP.ini settings for timeout? Execution times, memory etc.?

[Sickboards]

A dedicated server (AMD Ryzen 9 7950X 5.7GHz Turbo CPU
64 GB DDR5 RAM
2X1 TB NVMe U.2 SSD Storage)

so sufficient I would say

timeout is 2min

I don't think it my server...

[Nickz]

1 hour ago, Sickboards said:

I don't think it my server...

Depends on you php.ini settings  max_input_time  default is 60s I set that to 300 usually max_input_vars from 1000 to 10000.
You need yo restart apache2 to make changes settle.  

[Sickboards]

Thank you

but i rather change the SQL statement

[Nickz]

no problem, php.ini settings have an influence on SQL.

https://chrisshennan.com/blog/10-essential-phpini-tweaks-for-improved-web-performance