Slow query on table ps_cart_product

[virtual89]

Hello,

I'm using Prestashop 1.7.6.1, the site crashes because the query below takes about a minute.
The tables involved in the query are:
ps_cart_product (36860 rows)
ps_pack (0 rows)
ps_product (8068 rows)

How do I solve the problem?

Can I periodically empty ps_cart_product with a cron? Could this solve the problem?

I need a urgent solution to keep my site from goes in down again.

Please help me.
Thank you.

 

# Time: 210619 18:07:09
# Query_time: 54.646021  Lock_time: 26.371437  Rows_sent: 1  Rows_examined: 0
# Rows_affected: 0
SET timestamp=1624118829;
SELECT
            COALESCE(SUM(first_level_quantity) + SUM(pack_quantity), 0) as deep_quantity,
            COALESCE(SUM(first_level_quantity), 0) as quantity
          FROM (SELECT cp.`quantity` as first_level_quantity, 0 as pack_quantity
          FROM `ps_cart_product` cp
            WHERE cp.`id_product_attribute` = 391
            AND cp.`id_customization` = 0
            AND cp.`id_cart` = 0 AND cp.`id_product` = 1483 UNION SELECT 0 as first_level_quantity, cp.`quantity` * p.`quantity` as pack_quantity
          FROM `ps_cart_product` cp JOIN `ps_pack` p ON cp.`id_product` = p.`id_product_pack` JOIN `ps_product` pr ON p.`id_product_pack` = pr.`id_product`
            WHERE cp.`id_product_attribute` = 391
            AND cp.`id_customization` = 0
            AND cp.`id_cart` = 0 AND p.`id_product_item` = 1483 AND (pr.`pack_stock_type` IN (1,2) OR (
            pr.`pack_stock_type` = 3
            AND 0 = 1
        ))) as q LIMIT 1;

 

[virtual89]

Can anyone help me please?

[musicmaster]

36000 isn't that much that it should cause trouble. 

The real trouble is this query. It is looks overcomplicated. Where does it come from? What is it supposed to do? Can't it be rewritten?

As for cleaning carts. It is not advised to clean all carts. Your customers who are shopping at that moment won't like it if you do that. The most common option is to delete all abandoned carts after a certain period. Prestools has an option for that under Cleanup (it is free). I don't know tools that you could use with cron.

 

[PrestaHeroes USA]

upgrade hosting, now it's this query but there will be others...

[virtual89]

I upgraded the RAM, now it has 16GB and so far no problem.

[PrestaHeroes USA]

6 hours ago, virtual89 said:

I upgraded the RAM, now it has 16GB and so far no problem.

Something most overlook is using phpmyadmin stats, there you will find recommendations on how to improve your MySQL performance.  We have many clients who experience slow response, many run PS module cache's (do not use).  Tuning an application is expensive compared to a PS tuned hosting environment.  Look at ways to improve your hosting environment, i.e. php fpm'.   happy selling