Forum
Cart Table has over 6 million rows
Hi,
I've just downloaded a database dump of a client's website and noticed that it was taking an unusually long period of time, on closer inspection the perch2_shop_cart table has over 6,600,000 rows inside. They all appear to be blank:
| cartID | memberID | customerID | currencyID | locationID | shippingID | cartPricing | cartTotalItems | cartTotalProducts | cartTotalWithTax | cartTotalWithoutTax | billingAddress | shippingAddress | cartProperties | cartDiscountCode |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 4995064 | NULL | NULL | 47 | 1 | NULL | standard | 0 | 0 | 0.00 | 0.00 | NULL | NULL | [] |
The perch2_shop_cart_data table only contains around 900 records.
Is this something that is expected or does it suggest a problem somewhere with the checkout process?
Here is the diagnostics report:
Perch Runway: 2.8.34, PHP: 7.1.10-1+ubuntu16.04.1+deb.sury.org+1, MySQL: mysqlnd 5.0.12-dev - 20150407 - $Id: b396954eeb2d1d9ed7902b8bae237b287f21ad9e $, with PDO
Server OS: Linux, fpm-fcgi
Installed apps: content (2.8.34), assets (2.8.34), categories (2.8.34), root_analytics (1.0.1), perch_forms (1.8.3), root_heartbeat (1.0.1), perch_shop_orders (1.0.12), perch_shop_products (1.0.12), perch_shop (1.0.12), perch_members (1.5), root_artithmetic (1), root_breadcrumbs (1.0), perch_mailchimp (3.0.1)
App runtimes: <?php $apps_list = [ 'content', 'categories', 'perch_forms', 'perch_members', 'perch_shop', 'perch_mailchimp', 'root_breadcrumbs', 'root_arithmetic', ];
PERCH_LOGINPATH: /admin
PERCH_PATH: /home/forge/madelittle.co.uk/build/admin
PERCH_CORE: /home/forge/madelittle.co.uk/build/admin/core
PERCH_RESFILEPATH: /home/forge/madelittle.co.uk/build/admin/resources
Image manipulation: GD Imagick
PHP limits: Max upload 32M, Max POST 32M, Memory: 512M, Total max file upload: 32M
F1: 6a33f95eca3667f9e0c39bf5ca2980fe
Resource folder writeable: Yes
HTTP_HOST: madelittle.co.uk
DOCUMENT_ROOT: /home/forge/madelittle.co.uk/build
REQUEST_URI: /admin/core/settings/diagnostics/
SCRIPT_NAME: /admin/core/settings/diagnostics/index.php
Is doesn't sound ideal, but is it manifesting in any problems?
In terms of site performance, no. The site is still very fast and it doesn't seem to impact any actions that use the cart so I wouldn't have said it is an urgent issue.
Though it has ballooned the database to around 0.5 GB (though the server reports a data size of 2.1 GB) which does impact backups and resource usage - if there is no way for this data to self-clear then this is only going to become more of an issue over time.
Can you give an example of a blank row?
The table in the first post is one I copied out of the cart table. Raw data only looks like so:
It's written on page load when a new cart is initialised (from the debug log):
Have you excluded robots from your cart?
I believe up until around a month ago when we changed servers some would have been. There also isn't a single cart page, there is a global call to
perch_shop_cart_item_count ()in a header layout that displays in a bar across the top of the site (see below). In this case wouldn't there need to be a bot-block for all pages, which would impact search engines?I believe up until around a month ago when we changed servers some would have been. There also isn't a single cart page, there is a global call to
perch_shop_cart_item_count ()in a header layout that displays in a bar across the top of the site (see below). In this case wouldn't there need to be a bot-block for all pages, which would impact search engines?Ok, so
perch_shop_cart_item_count()will create the cart if it doesn't exist yet.Yes, I'd traced through the code to find that was where the blank records are coming from. Is there not a way to check whether a cart exists without initialising a new one?
No, if you try to interact with the cart it will initialise the cart.
Could I put in a feature request for a future version of shop for a new runtime function such as
perch_shop_cart_exists()which doesn't trigger a new cart - for the use case in my previous post (which is an accidental double post, not sure how that happened - sorry!).Unless there is a better way of displaying a cart total in a global header that won't cause this problem?