Order By Rand() In Category.php Is Pure Evil? Way To Optimize Rand Queries

[hakeryk2]

Hello community,

 

At the beginning I want to say that I am not professional programmer - just an amateur who sometimes edit something that is why I am asking You for help that I think will help a lot of people. IF I AM WRONG OR I MISUNDERSTAND SOMETHING PLEASE TELL ME THAT.

 

I am using Vekia's Prestashop Related Product PRO plugin that is really helpful when it comes to show some random products from the same category but it is using default Prestashop order by rand method and when I enable this method to show 24 random products that product page idle loading time is from 4000ms to 7000ms because it is waiting for database to show some random products. When I reduce it to 8 products it is 1500-2000ms but it is still too long when it comes to SEO score. I was looking for solution in the pluging but I couldn't figure it out but I found this:

 

Presta 1.6.1.4 here. In classes\Category.php something about line 744 we have something like this:

$sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) AS quantity'.(Combination::isFeatureActive() ? ', IFNULL(product_attribute_shop.id_product_attribute, 0) AS id_product_attribute,
					product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity' : '').', pl.`description`, pl.`description_short`, pl.`available_now`,
					pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, image_shop.`id_image` id_image,
					il.`legend` as legend, m.`name` AS manufacturer_name, cl.`name` AS category_default,
					DATEDIFF(product_shop.`date_add`, DATE_SUB("'.date('Y-m-d').' 00:00:00",
					INTERVAL '.(int)$nb_days_new_product.' DAY)) > 0 AS new, product_shop.price AS orderprice
				FROM `'._DB_PREFIX_.'category_product` cp
				LEFT JOIN `'._DB_PREFIX_.'product` p
					ON p.`id_product` = cp.`id_product`
				'.Shop::addSqlAssociation('product', 'p').
                (Combination::isFeatureActive() ? ' LEFT JOIN `'._DB_PREFIX_.'product_attribute_shop` product_attribute_shop
				ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop='.(int)$context->shop->id.')':'').'
				'.Product::sqlStock('p', 0).'
				LEFT JOIN `'._DB_PREFIX_.'category_lang` cl
					ON (product_shop.`id_category_default` = cl.`id_category`
					AND cl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('cl').')
				LEFT JOIN `'._DB_PREFIX_.'product_lang` pl
					ON (p.`id_product` = pl.`id_product`
					AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').')
				LEFT JOIN `'._DB_PREFIX_.'image_shop` image_shop
					ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop='.(int)$context->shop->id.')
				LEFT JOIN `'._DB_PREFIX_.'image_lang` il
					ON (image_shop.`id_image` = il.`id_image`
					AND il.`id_lang` = '.(int)$id_lang.')
				LEFT JOIN `'._DB_PREFIX_.'manufacturer` m
					ON m.`id_manufacturer` = p.`id_manufacturer`
				WHERE product_shop.`id_shop` = '.(int)$context->shop->id.'
					AND cp.`id_category` = '.(int)$this->id
                    .($active ? ' AND product_shop.`active` = 1' : '')
                    .($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '')
                    .($id_supplier ? ' AND p.id_supplier = '.(int)$id_supplier : '');

        if ($random === true) {
            $sql .= ' ORDER BY RAND() LIMIT '.(int)$random_number_products;
        } else {
            $sql .= ' ORDER BY '.(!empty($order_by_prefix) ? $order_by_prefix.'.' : '').'`'.bqSQL($order_by).'` '.pSQL($order_way).'
			LIMIT '.(((int)$p - 1) * (int)$n).','.(int)$n;
        }

and if I am not wrong this is responsible for showing off products on categories pages (including some plugins as well).

As You can see there is a line 

if ($random === true) {
            $sql .= ' ORDER BY RAND() LIMIT '.(int)$random_number_products;
        }

and IMHO this is the key when we can start some changes. I found an articles about optimizing mysql order by rand query with some really satisfying results. You can read them here

 

https://www.warpconduit.net/2011/03/23/selecting-a-random-record-using-mysql-benchmark-results/

 

and here

 

http://jan.kneschke.de/projects/mysql/order-by-rand/ (in this case the results were just amazing)

 

But there is a case. My programming skills are limited to implement those methods into Prestashop    This is just way complicated for me so can someone help me with editing those lines with one of new methods. Can anyone with better experience and knowledge can help me? I will be very grateful.

 

Maybe this will be another thing to implement in new prestashop version as well. All optimizing things should be welcome in Prestashop

 

 

[hakeryk2]

Anyone? I think that everyone will have benefits from this. That's why I am bumping this thread.

 

I tried with really simple solutions like 

$sql .= ' AND RAND()<(SELECT (('.(int)$random_number_products.'/COUNT(*))*10) FROM `'._DB_PREFIX_.'category_product`) ORDER BY RAND() LIMIT '.(int)$random_number_products;

but it was not working well - it was showing only few (randomly, 2 or 3 or more) random products, but not ie 8 like I want.

 

EDIT NUMBER 2: Solution a little bit faster

With help from StackOverflow I have something like this:

 $sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) AS quantity'.(Combination::isFeatureActive() ? ', IFNULL(product_attribute_shop.id_product_attribute, 0) AS id_product_attribute,
					product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity' : '').', pl.`description`, pl.`description_short`, pl.`available_now`,
					pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, image_shop.`id_image` id_image,
					il.`legend` as legend, m.`name` AS manufacturer_name, cl.`name` AS category_default,
					DATEDIFF(product_shop.`date_add`, DATE_SUB("'.date('Y-m-d').' 00:00:00",
					INTERVAL '.(int)$nb_days_new_product.' DAY)) > 0 AS new, product_shop.price AS orderprice,
					(SELECT COUNT(*) FROM `'._DB_PREFIX_.'product`) * RAND() AS selector
				FROM `'._DB_PREFIX_.'category_product` cp
				LEFT JOIN `'._DB_PREFIX_.'product` p
					ON p.`id_product` = cp.`id_product`
				'.Shop::addSqlAssociation('product', 'p').
                (Combination::isFeatureActive() ? ' LEFT JOIN `'._DB_PREFIX_.'product_attribute_shop` product_attribute_shop
				ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop='.(int)$context->shop->id.')':'').'
				'.Product::sqlStock('p', 0).'
				LEFT JOIN `'._DB_PREFIX_.'category_lang` cl
					ON (product_shop.`id_category_default` = cl.`id_category`
					AND cl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('cl').')
				LEFT JOIN `'._DB_PREFIX_.'product_lang` pl
					ON (p.`id_product` = pl.`id_product`
					AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').')
				LEFT JOIN `'._DB_PREFIX_.'image_shop` image_shop
					ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop='.(int)$context->shop->id.')
				LEFT JOIN `'._DB_PREFIX_.'image_lang` il
					ON (image_shop.`id_image` = il.`id_image`
					AND il.`id_lang` = '.(int)$id_lang.')
				LEFT JOIN `'._DB_PREFIX_.'manufacturer` m
					ON m.`id_manufacturer` = p.`id_manufacturer`
				WHERE product_shop.`id_shop` = '.(int)$context->shop->id.'
					AND cp.`id_category` = '.(int)$this->id
                    .($active ? ' AND product_shop.`active` = 1' : '')
                    .($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '')
                    .($id_supplier ? ' AND p.id_supplier = '.(int)$id_supplier : '');

        if ($random === true) {
            $sql .= ' HAVING selector <= '. (900+$random_number_products);
            $sql .= ' ORDER BY selector LIMIT '.(int)$random_number_products;
        }

Now it is only max 1500ms idle time for 16 products ... but it is sometimes showing less then 16 products (it is quite random and it is affected by $sql .= ' HAVING selector <= '. (900+$random_number_products); but I don't have a clue how to figure that out.

Edited April 2, 2016 by hakeryk2 (see edit history)

[hakeryk2]

If someone is not feeling very good at editing this then I created override for this function. Just download the file and put it into yourdomain.com/override/classes/ . After that delete the file class_index.php from yourdomain.com/cache.

 

It should speed up your site when usind Related Products Pro or Random Products from Category (productscategory) module and any part which using random showing up the products.

 

Category.php

 

P.S Maybe there will be some uber pro guy who will look into this code and make it even more faster?

 

I will put some benchmark later and I will let You know how much more faster is that override and how light is for CPU and SQL.

 

P.S I edited the file so it has 0 downloads right now. Feel free to download it and share you experience.

Edited July 31, 2016 by hakeryk2 (see edit history)