Slow x-cart cart page

Question

I have a x-cart 4.1.3 webshop with PHP 5.3 in cart.php you have 2 requests with LEFT JOIN is it possible to change that to INNER JOIN ?

Or is any other optimisation possible ?

Here the code:

$products_array = func_query_first("SELECT $sql_tbl[products].*, MIN($sql_tbl[pricing].price) as price, IF($sql_tbl[images_T].id IS NULL, '', 'Y') as is_thumbnail, $sql_tbl[images_T].image_path, $sql_tbl[images_T].image_x, $sql_tbl[images_T].image_y, IF($sql_tbl[images_P].id IS NULL, '', 'P') as is_pimage, $sql_tbl[images_P].image_path as pimage_path, $sql_tbl[images_P].image_x as pimage_x, $sql_tbl[images_P].image_y as pimage_y FROM $sql_tbl[pricing],$sql_tbl[products] LEFT JOIN $sql_tbl[images_T] ON $sql_tbl[images_T].id = $sql_tbl[products].productid LEFT JOIN $sql_tbl[images_P] ON $sql_tbl[images_P].id = $sql_tbl[products].productid WHERE $sql_tbl[products].productid=$sql_tbl[pricing].productid AND $sql_tbl[products].forsale != 'N' AND $sql_tbl[products].productid='$productid' AND $avail_condition $sql_tbl[pricing].quantity<='$amount' AND $sql_tbl[pricing].membershipid IN('$membershipid', 0) AND $sql_tbl[pricing].variantid = '$variantid' GROUP BY $sql_tbl[products].productid ORDER BY $sql_tbl[pricing].quantity DESC");

And:

$discount_info = func_query_first("SELECT $sql_tbl[discounts].*, $max_discount_str FROM $sql_tbl[discounts] LEFT JOIN $sql_tbl[discount_memberships] ON $sql_tbl[discounts].discountid = $sql_tbl[discount_memberships].discountid WHERE minprice<='$avail_discount_total' $provider_condition AND ($sql_tbl[discount_memberships].membershipid IS NULL OR $sql_tbl[discount_memberships].membershipid = '$membershipid') ORDER BY max_discount DESC");

Answer 1

1) For the first one, it is possible if you uploaded product/thumbnail images for all the products, otherwise, they will disappear from the storefront.

2) As for the second one, you have to make sure that you have at least one row in xcart_discount_memberships per each row in xcart_discounts

For that, add the proper INSERT...xcart_discount_memberships after the code

provider/discounts.php:128:         $_id = func_array2insert("discounts",
provider/discounts.php-129-             array(
provider/discounts.php-130-                 "minprice" => $minprice_new,
provider/discounts.php-131-                 "discount" => $discount_new,
provider/discounts.php-132-                 "discount_type" => $discount_type_new,
provider/discounts.php-133-                 "provider" => $login

As an alternative, you can add an SQL trigger to insert/delete rows to the xcart_discount_memberships table in an automatic way.

General suggestions

1) Use the EXPLAIN https://dev.mysql.com/doc/refman/8.0/en/explain.html to find out where indexes should be added.

2) Apply the performance tips https://help.x-cart.com/X-Cart:Performance_tips

3) Use the internal X-Cart bench feature, activated by the patch to find bottlenecks.

index 7020274df6..2e3166b97d 100644
--- a/top.inc.php
+++ b/top.inc.php
@@ -50,17 +50,17 @@ define('LOG_WITH_BACKTRACE', false);
 #
 # Switching on the internal performance measurement mechanism
 #
-define('BENCH', false);
+define('BENCH', true);

 #
 # Do not display the performance report
 #
-define('BENCH_SIMPLE', true);
+define('BENCH_SIMPLE', false);

 #
 # Show tracing
 #
-define('BENCH_BACKTRACE', false);
+define('BENCH_BACKTRACE', true);

 #
 # Disable creation of binary files with results of performance tests
@@ -92,7 +92,7 @@ define('BENCH_LOG_TYPE_LIMIT', "");
 # T - only total values
 # F - full report
 # A - advanced report
-define('BENCH_DISPLAY_TYPE', "T");
+define('BENCH_DISPLAY_TYPE', "A");

 #
 # Code execution time, threshold value

Here is a man how to apply patches https://help.x-cart.com/X-Cart:Applying_Patches#To_apply_a_patch_manually