Prestashop product ORDER BY RAND optimization

Question

I am using 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 a 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). Really bad order by rand(). As You can see there is a line

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

and in my opinion this is the key when we can start some changes. I found an article about optimizing MySQL ORDER BY RAND queries 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? Or invent something which is better then those which I proposed?

Answer 1

Let's assume you want to present a choice of k = $random_number_products from within the set of all n rows in your prefix_product table. That means you're hoping to choose k / n of your rows randomly.

RAND() generates a pseudorandom number in the range [0,1]. So to implement k / n choice, you need RAND() <= k / n or, moving it to the integer comparison domain, n*RAND() <= k . If your application will fail in the case that your query chooses too few random rows, you need to boost up your k value to increase the probability of any row being chosen. Let's just say k+5 for good measure.

Then you need to add something to the end of the SELECT clause in your query, right after orderprice, like this:

      SELECT...,
      INTERVAL '.(int)$nb_days_new_product.' DAY)) > 0 AS new, product_shop.price AS orderprice,
      (SELECT COUNT(*) FROM `'._DB_PREFIX_.'product`) * RAND() AS selector
      ...

This assigns a random selector between 0 and the COUNT(*) value to each row in the result set.

Finally, at the end of your query put this.

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

I think this will work.

• HAVING chooses a subset of your rows. You need HAVING rather than WHERE for this particular application, because it refers to a generated column.
• 5+ overestimates the size of that subset slightly.
• ORDER BY randomizes the order of the chosen rows.
• LIMIT gets rid of any extra rows resulting from your overestimate.

I may have left in some syntax errors. If so, I beg your pardon.

ORDER BY RAND() LIMIT n is an especially smelly case of the nasty antipattern ORDER BY anything LIMIT n. It wastes server resources. It generates the entire result set (in server RAM or on disk if it doesn't fit in RAM), then sorts it into some kind of order, then returns a few rows, than discards the rest. The secret to good performance in these cases is to discard rows as early as possible, and to sort the smallest result set.

But it works. So if the query runs infrequently, just keep it. In your case the query runs frequently.

(Prestashop? ORDER BY RAND()? Really? When you get this working send them a bug report with your fix in place.)

Answer 2

Virtually all algorithms out there are O(N) or worse. In my blog on faster random searches, I link to them as 'not adequately fast', including jan's classic page. I present 5 cases; I don't know which can be applied to your situation:

• Case: Consecutive AUTO_INCREMENT without gaps, 1 row returned
• Case: Consecutive AUTO_INCREMENT without gaps, 10 rows
• Case: AUTO_INCREMENT with gaps, 1 row returned
• Case: Extra FLOAT column for randomizing
• Case: UUID or MD5 column

All cases run faster than a full table scan.