Forum

Thread tagged as: Problem, Addons, Shop

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
James Wigger

James Wigger 0 points

  • 3 years ago
Drew McLellan

Drew McLellan 2638 points
Perch Support

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.

Drew McLellan

Drew McLellan 2638 points
Perch Support

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:

4495090 NULL    NULL    47  1   NULL    standard    0   0   0.00    0.00    NULL    NULL    []  

It's written on page load when a new cart is initialised (from the debug log):

[1] SELECT locationID FROM perch2_shop_tax_locations WHERE locationIsHome=1 LIMIT 1
1.4482  0.0005  INSERT INTO perch2_shop_cart(memberID,locationID,currencyID,cartPricing,cartProperties) VALUES(NULL,'1',47,'standard','[]')
1.4526  0.0045  [1] SELECT * FROM perch2_shop_cart WHERE cartID=6886100
Drew McLellan

Drew McLellan 2638 points
Perch Support

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?

<?php if(perch_shop_cart_item_count([], true) > 0): ?>
<a href="<?php echo SHOP_PATH_BASKET; ?>" class="nav-link u-uppercase">
    <img
        src="/images/icons/basket.png"
        srcset="/images/icons/basket@2x.png 2x, /images/icons/basket@3x.png 3x"
        alt="Basket"
        class="nav-link__icon"
    />
    Basket (<?php perch_shop_cart_item_count(); ?>)
</a>
<?php endif; ?>

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?

<?php if(perch_shop_cart_item_count([], true) > 0): ?>
<a href="<?php echo SHOP_PATH_BASKET; ?>" class="nav-link u-uppercase">
    <img
        src="/images/icons/basket.png"
        srcset="/images/icons/basket@2x.png 2x, /images/icons/basket@3x.png 3x"
        alt="Basket"
        class="nav-link__icon"
    />
    Basket (<?php perch_shop_cart_item_count(); ?>)
</a>
<?php endif; ?>
Drew McLellan

Drew McLellan 2638 points
Perch Support

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?

Drew McLellan

Drew McLellan 2638 points
Perch Support

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?