Improving MySQL Queries in your CubeCart V4 E-Commerce Store

Improving MySQL Queries in your CubeCart V4 Store.

Although CubeCart V4 is no longer an available product or is even supported by the CubeCart development team (Havenswift Hosting will still provide support for all clients that have one of our E-Commerce Shared Hosting packages !) there are a huge number of websites that for a variety of reasons are still running this version and will continue to do so for a long time to come. As CubeCart uses MySQL quite heavily it is very important that you choose a hosting company that :

• Provides servers that arent oversold as this reduces the amount of memory available for the MySQL process which is critical to the performance
• Understands how to tune MySQL for it’s customer base – we are constantly amazed how many hosting companies we come across, both large and small, that dont understand how to tune MySQL correctly or worse still leave it in it’s standard configuration

Needless to say, Havenswift Hosting do this and a whole lot more for our E-Commerce Shared Hosting clients !

As far as changes that you as a store owner can make to your website, here is a list of some of most common ones that will have the greatest effect on your website

Switch on CubeCart SQL Query Caching

In the “General Settings” section of the CubeCart Administration panel you will find an option to “Use SQL Query Caching (Recommended!)” – this should be set to YES by default but we have often found this set to NO on sites. Enabling this option results in far fewer queries being made against the database, as once a query has been executed the results are stored in a cached file. These cached results can be accessed much faster than re-doing the queries again, which makes any page that needs those results, load much faster not just for that visitor but for every site visitor.

Correctly Removing CubeCart Random Product functionality

Many store owners decide that they don’t want this functionality on their website and simply remove the {RANDOM_PROD} tag from the styleTemplates/global/index.tpl file for their chosen skin. While this stops it from being displayed, the underlying query is still run for each and every page that is visited and if you say 1500 visitors per day who each click through 20 pages, this alone results in 30,000 extra queries per day being needlessly run.

To stop this query being run you need to also make the following code change so that the PHP code that runs the query which is found in includes/boxes/randomProd.inc.php is not included. We can do this as follows:

Open: includes/global/index.inc.php

Find: around lines 57-58

require_once"includes".CC_DS."boxes".CC_DS."randomProd.inc.php";
$body->assign("RANDOM_PROD",$box_content);

Change To:

//require_once"includes".CC_DS."boxes".CC_DS."randomProd.inc.php";
//$body->assign("RANDOM_PROD",$box_content);

Open: includes/global/cart.inc.php

Find: around lines 99-100

require_once"includes".CC_DS."boxes".CC_DS."randomProd.inc.php";
$body->assign("RANDOM_PROD",$box_content);

Change To:

//require_once"includes".CC_DS."boxes".CC_DS."randomProd.inc.php";
//$body->assign("RANDOM_PROD",$box_content);

Correctly Removing CubeCart Popular Product functionality

Another common box that many users remove from their skin is {POPULAR_PRODUCTS} and in the same way as for the {RANDOM_PROD} example above, simply removing this tag from the styleTemplates/global/index.tpl file for your skin can allow an unnecessary query to be run for every page view. So to correctly remove this, you also need to make the following code change

Open: includes/global/index.inc.php

Find: around lines 72-73

require_once"includes".CC_DS."boxes".CC_DS."popularProducts.inc.php";
$body->assign("POPULAR_PRODUCTS",$box_content);

Change To:

//require_once"includes".CC_DS."boxes".CC_DS."popularProducts.inc.php";
//$body->assign("POPULAR_PRODUCTS",$box_content);

Open: includes/global/cart.inc.php

Find: around lines 108-109

require_once"includes".CC_DS."boxes".CC_DS."popularProducts.inc.php";
$body->assign("POPULAR_PRODUCTS",$box_content);

Change To:

//require_once"includes".CC_DS."boxes".CC_DS."popularProducts.inc.php";
//$body->assign("POPULAR_PRODUCTS",$box_content);

Caching CubeCart Popular Product Results

If you do display the Popular Products box on your site then within the Admin General Settings section there is a choice of what data is used to display the “Source for popular products data:” – this can either be “Number of Sales” or “Number of Views” If you choose “Number of Sales” to determine how popular a product is, then this query is already cached but if you choose “Number of Views” then this query is not cached which again means that each page view within your store runs this complex query !

Open: includes/boxes/popularProducts.inc.php

Find: around line 46

$popularProds = $db->select("SELECT I.name, I.productId FROM ".$glob['dbprefix']."CubeCart_inventory AS I, ".$glob['dbprefix']."CubeCart_category AS C WHERE C.cat_id = I.cat_id AND I.cat_id > 0 AND I.disabled = '0' AND (C.cat_desc != '##HIDDEN##' OR C.cat_desc IS NULL) ORDER BY I.popularity DESC",$config['noPopularBoxItems']);

Change To:

if (!$cache->cacheStatus) {
$popularProds = $db->select("SELECT I.name, I.productId FROM ".$glob['dbprefix']."CubeCart_inventory AS I, ".$glob['dbprefix']."CubeCart_category AS C WHERE C.cat_id = I.cat_id AND I.disabled = '0' AND (C.cat_desc != '##HIDDEN##' OR C.cat_desc IS NULL) ORDER BY I.popularity DESC",$config['noPopularBoxItems']);
$cache->writeCache($popularProds);
}
## $popularProds = $db->select("SELECT I.name, I.productId FROM ".$glob['dbprefix']."CubeCart_inventory AS I, ".$glob['dbprefix']."CubeCart_category AS C WHERE C.cat_id = I.cat_id AND I.cat_id > 0 AND I.disabled = '0' AND (C.cat_desc != '##HIDDEN##' OR C.cat_desc IS NULL) ORDER BY I.popularity DESC",$config['noPopularBoxItems']);

Making this change means that both types of queries are now cached and that the list of Popular products will not be 100% accurate (it is accurate to the last time that an admin user cleared cache so this should now be done every couple of weeks) but will again significantly reduce the number of queries being done

Disabling “Customers who bought this, also bought”

For stores with a large number of orders, the way this query is written often results in a massive overhead and seriously degraded performance. There are two options to solve this problem which are to

• Simply remove this functionality from your store
• Regularly archive older orders out of the two order tables

Improving Use of Indexes with CubeCart Queries

This is a complex area and there is not one simple solution that applies to all stores – much depends on the sizes of specific tables especially the inventory, category and order tables.

An index on a table acts like a pointer to the table rows, allowing a query to quickly determine which rows match a condition in the WHERE clause of a SELECT statement. When doing a query without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows and the larger the table, the more time this takes. If the table has an index for the columns used in the WHERE clause, then it can quickly determine the position in the middle of the data file to start retrieving matching records without having to look at all the data. According to the MySQL documentation “If a table has 1,000 rows, this is at least 100 times faster than reading sequentially.” There are a huge number of queries in CubeCart where the WHERE clause uses columns that are not indexed but it isnt always just a simple case of adding an index to any column that isnt already indexed !

One fairly simple example is that there a many queries that have a WHERE condition cat_id > 0 which immediately causes a table scan through every record of the inventory table. This is really bad for stores with tens or hundreds of thousands of products but what is worse is that the value in this column should never equal 0 ! The only place this can ever be set to zero is if the Import Catalogue routine is used and then the products are not assigned to a category.

The following two tabs change content below.

• Bio
• Latest Posts

Havenswift Hosting

This is the main Havenswift Hosting company account that is used by different members of staff when making blog postings on behalf of the company rather than as individuals

Latest posts by Havenswift Hosting (see all)

• CubeCart PayPal Commerce Plugin Requires Mandatory Phone Number - 14th April 2021
• PHP 7.2 Patches are available for Magento 1 - 6th October 2018
• New Magento patch released – SUPEE-10888 - 20th September 2018