Jump to content

Slow query on table ps_cart_product


virtual89

Recommended Posts

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;

 

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...