MySQL query long time

[mesas]

Hello,

our web shop has 10.000 products.

Also we notice that MySQL is using more than 100% CPU and after that we check which query is running we find these result:

MariaDB [(none)]> SHOW PROCESSLIST;
+--------+----------------+-----------------+--------------+---------+------+------------+------------------------------------------------------------------------------------------------------+----------+
| Id     | User           | Host            | db           | Command | Time | State      | Info                                                                                                 | Progress |
+--------+----------------+-----------------+--------------+---------+------+------------+------------------------------------------------------------------------------------------------------+----------+
| 220117 | procomp_userv2 | localhost:60606 | procomp_dbv2 | Query   | 9094 | Statistics | SELECT p.id_product, p.price, psi.price_min, psi.price_max FROM (SELECT p.id_product, p.id_manufactu |    0.000 |
| 220462 | procomp_userv2 | localhost:33578 | procomp_dbv2 | Query   | 8997 | Statistics | SELECT p.id_product, p.price, psi.price_min, psi.price_max FROM (SELECT p.id_product, p.id_manufactu |    0.000 |
| 220482 | procomp_userv2 | localhost:33656 | procomp_dbv2 | Query   | 8991 | Statistics | SELECT p.id_product, p.price, psi.price_min, psi.price_max FROM (SELECT p.id_product, p.id_manufactu |    0.000 |
| 220696 | procomp_userv2 | localhost:34416 | procomp_dbv2 | Query   | 8930 | Statistics | SELECT p.id_product, p.price, psi.price_min, psi.price_max FROM (SELECT p.id_product, p.id_manufactu |    0.000 |
| 220786 | procomp_userv2 | localhost:34722 | procomp_dbv2 | Query   | 8902 | Statistics | SELECT p.id_product, p.price, psi.price_min, psi.price_max FROM (SELECT p.id_product, p.id_manufactu |    0.000 |
| 223624 | procomp_userv2 | localhost:44554 | procomp_dbv2 | Query   | 8042 | Statistics | SELECT p.id_product, p.price, psi.price_min, psi.price_max FROM (SELECT p.id_product, p.id_manufactu |    0.000 |
| 223697 | procomp_userv2 | localhost:44792 | procomp_dbv2 | Query   | 8021 | Statistics | SELECT p.id_product, p.price, psi.price_min, psi.price_max FROM (SELECT p.id_product, p.id_manufactu |    0.000 |
| 223815 | procomp_userv2 | localhost:45206 | procomp_dbv2 | Query   | 7981 | Statistics | SELECT p.id_product, p.price, psi.price_min, psi.price_max FROM (SELECT p.id_product, p.id_manufactu |    0.000 |
| 223929 | procomp_userv2 | localhost:45664 | procomp_dbv2 | Query   | 7940 | Statistics | SELECT p.id_product, p.price, psi.price_min, psi.price_max FROM (SELECT p.id_product, p.id_manufactu |    0.000 |
| 224010 | procomp_userv2 | localhost:45966 | procomp_dbv2 | Query   | 7901 | Statistics | SELECT p.id_product, p.price, psi.price_min, psi.price_max FROM (SELECT p.id_product, p.id_manufactu |    0.000 |
| 224068 | procomp_userv2 | localhost:46160 | procomp_dbv2 | Query   | 7881 | Statistics | SELECT p.id_product, p.price, psi.price_min, psi.price_max FROM (SELECT p.id_product, p.id_manufactu |    0.000 |
| 250295 | root           | localhost       | NULL         | Query   |    0 | starting   | SHOW PROCESSLIST                                                                                     |    0.000 |
+--------+----------------+-----------------+--------------+---------+------+------------+------------------------------------------------------------------------------------------------------+----------+
12 rows in set (0.000 sec)

 

There is some kind issue because these query doesnt ends and overload server.

Also, we allready use Prestashop 1.6 latest few years and now we make clean installation of version 1.7.8.8. import products and before 2 days we put new web shop live.

Until now we remove al Statistics modules, disable and uninstall. 
Second thing what we try is to make multiple templates (not only one) in Faceted module which we use for filters.

Thank you.

Regards,

Edited June 28, 2023 by mesas (see edit history)

[mesas]

I investigate these issue with long query and figure out that source is Faceted module.  

During day I dont have issue, query is done but during night something happen and query stuck and become long query.
Also I run cronjob for price indexer for each 30 minutes.

In attachment you will find query from database.
 

 

full query.zip

Edited June 28, 2023 by mesas (see edit history)