Adding 21,000 discount codes to cart rules

[leonetrek]

I added 21,000 discount codes to the cart rules with different numbers. 

 

7,000 for one item

7,000 for second item

7,000 for the third item

 

After this, the site is very slow after adding anything to the cart (it loads slowly and adding to cart or deleting an item from the cart is very slow).

 

I checked the database, and saw that the entries for these codes are located in 5 tables with 100,000 entries in those tables. Can this affect the site speed?

I using Prestashop 1.6.0.11

 

Here is the link to the site on a test domain so you can check how slow the site is loading - http://www2.sovkaz.com/

Edited January 23, 2015 by leonetrek (see edit history)

[leonetrek]

I turned on Debug mode and saw that there were over 150 number of queries into the database, without the coupons. After I added coupons, the number of queries increased to over 80000. Why are there queries sent into the database if I simply added the coupons and that's it. The site is currently in debug mode and I left 3 coupons.  With each coupon I add, increases the number of queries 4 times. 

[vekia]

hello

sadly prestashop check each cart rule that you have in your store

it's because of:

 

/classes/cartRule.php

	public static function autoAddToCart(Context $context = null)
	{
		if ($context === null)
			$context = Context::getContext();
		if (!CartRule::isFeatureActive() || !Validate::isLoadedObject($context->cart))
			return;

		$sql = '
		SELECT cr.*
		FROM '._DB_PREFIX_.'cart_rule cr
		LEFT JOIN '._DB_PREFIX_.'cart_rule_shop crs ON cr.id_cart_rule = crs.id_cart_rule
		'.(!$context->customer->id && Group::isFeatureActive() ? ' LEFT JOIN '._DB_PREFIX_.'cart_rule_group crg ON cr.id_cart_rule = crg.id_cart_rule' : '').'
		LEFT JOIN '._DB_PREFIX_.'cart_rule_carrier crca ON cr.id_cart_rule = crca.id_cart_rule
		'.($context->cart->id_carrier ? 'LEFT JOIN '._DB_PREFIX_.'carrier c ON (c.id_reference = crca.id_carrier AND c.deleted = 0)' : '').'
		LEFT JOIN '._DB_PREFIX_.'cart_rule_country crco ON cr.id_cart_rule = crco.id_cart_rule
		WHERE cr.active = 1
		AND cr.code = ""
		AND cr.quantity > 0
		AND cr.date_from < "'.date('Y-m-d H:i:s').'"
		AND cr.date_to > "'.date('Y-m-d H:i:s').'"
		AND (
			cr.id_customer = 0
			'.($context->customer->id ? 'OR cr.id_customer = '.(int)$context->cart->id_customer : '').'
		)
		AND (
			cr.`carrier_restriction` = 0
			'.($context->cart->id_carrier ? 'OR c.id_carrier = '.(int)$context->cart->id_carrier : '').'
		)
		AND (
			cr.`shop_restriction` = 0
			'.((Shop::isFeatureActive() && $context->shop->id) ? 'OR crs.id_shop = '.(int)$context->shop->id : '').'
		)
		AND (
			cr.`group_restriction` = 0
			'.($context->customer->id ? 'OR 0 < (
				SELECT cg.`id_group`
				FROM `'._DB_PREFIX_.'customer_group` cg
				INNER JOIN `'._DB_PREFIX_.'cart_rule_group` crg ON cg.id_group = crg.id_group
				WHERE cr.`id_cart_rule` = crg.`id_cart_rule`
				AND cg.`id_customer` = '.(int)$context->customer->id.'
				LIMIT 1
			)' : (Group::isFeatureActive() ? 'OR crg.`id_group` = '.(int)Configuration::get('PS_UNIDENTIFIED_GROUP') : '')).'
		)
		AND (
			cr.`reduction_product` <= 0
			OR cr.`reduction_product` IN (
				SELECT `id_product`
				FROM `'._DB_PREFIX_.'cart_product`
				WHERE `id_cart` = '.(int)$context->cart->id.'
			)
		)
		AND cr.id_cart_rule NOT IN (SELECT id_cart_rule FROM '._DB_PREFIX_.'cart_cart_rule WHERE id_cart = '.(int)$context->cart->id.')
		ORDER BY priority';
		$result = Db::getInstance()->executeS($sql);
		if ($result)
		{
			$cart_rules = ObjectModel::hydrateCollection('CartRule', $result);
			if ($cart_rules)
				foreach ($cart_rules as $cart_rule)
					if ($cart_rule->checkValidity($context, false, false))
						$context->cart->addCartRule($cart_rule->id);
		}
	}

can you for test purposes only, comment it out:

 

controllers/front/orderController.php, line ~302

			CartRule::autoAddToCart($this->context);

note: this is for test purposes only

[leonetrek]

hello

sadly prestashop check each cart rule that you have in your store

Thanks for the reply.

 

But this does not solve the problem

[vekia]

you mean, that you removed temporarily code that i suggested and still the same?

[leonetrek]

When I tried out what you suggested, the site started working faster. I added all the 5,000 discount codes but then it drastically slowed down again and the number of queries into the database didn't decrease.

 

SQL Queries: 30154 queries in 39235 ms - 5,000 discount codes

Edited January 24, 2015 by leonetrek (see edit history)

[leonetrek]

On another domain I installed Prestashop 1.5.6.2 and uploaded 60,000 discount codes and everything works perfectly except for a slight delay when inputing the actual code in the cart.

Edited January 24, 2015 by leonetrek (see edit history)

[leonetrek]

you mean, that you removed temporarily code that i suggested and still the same?

Yes, that line is deleted now

[leonetrek]

I solved the issue and now the site works fast with 100,000 discount codes. I deleted the following on line 255 in /classes/cartRule.php:

		$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
		SELECT *
		FROM `'._DB_PREFIX_.'cart_rule` cr
		LEFT JOIN `'._DB_PREFIX_.'cart_rule_lang` crl ON (cr.`id_cart_rule` = crl.`id_cart_rule` AND crl.`id_lang` = '.(int)$id_lang.')
		WHERE (
			cr.`id_customer` = '.(int)$id_customer.' OR cr.group_restriction = 1
			'.($includeGeneric ? 'OR cr.`id_customer` = 0' : '').'
		)
		AND cr.date_from < "'.date('Y-m-d H:i:s').'"
		AND cr.date_to > "'.date('Y-m-d H:i:s').'"
		'.($active ? 'AND cr.`active` = 1' : '').'
		'.($inStock ? 'AND cr.`quantity` > 0' : ''));

If anyone knows, would this affect the site somehow?

Edited January 28, 2015 by leonetrek (see edit history)

[Skumbabs]

Did you see something wrong with this changes ?

Thanks