Limit query for random products module to increase performance

[k92]

Hi,

I'm using Joomla 3.7.3 and VirtueMart 3.2.2 with PHP 7.0.
I have about 10.000 products, though performance is quite good so far.

The only thing I'm struggeling with is the incredible huge loading time of two of my modules where I'm showing random and new products.
I'm using the default VirtueMart products module (mod_virtuemart_product) with "random products" (respectively "new products") selected.

First my intention was to display about 100 products there but I always get timeouts after a couple of seconds (Error 500).
Even limiting the amount of displayed products to 12 helps only a bit. The page loads but it takes more than 15 seconds until it's finished.
Sure I could extend the maximum execution time but visitors won't wait 20 seconds for a page loading.

Since I don't have any major issues with performance on other pages of my shop, I guess the problem is the quite big amount of products in my store.

My solution approach is to limit the sql query:
The module shouldn't try to receive 100 random products out of 10.000 but 100 random products out of 500 e.g.
I guess it takes too long to get a couple of random / new products from a total of 10.000.

Could anybody please tell me if my approach makes sense and if so how it could be done?

I guess the query is built in administrator/components/com_virtuemart/models/product.php but limiting the query via 'LIMIT 100' doesn't work since the limiting is done anywhere else.

Edit:
That's how administrator/components/com_virtuemart/models/product.php looks like

Code Select Expand

//Group case from the modules
if ($group) {

$latest_products_days = VmConfig::get ('latest_products_days', 7);
$latest_products_orderBy = VmConfig::get ('latest_products_orderBy','created_on');
$groupBy = 'group by p.`virtuemart_product_id` ';
switch ($group) {
case 'featured':
$where[] = 'p.`product_special`="1" ';
$orderBy = 'ORDER BY RAND()';
break;
case 'discontinued':
$where[] = 'p.`product_discontinued`="1" ';
$orderBy = 'ORDER BY RAND()';
break;
case 'latest':
$orderBy = 'ORDER BY p.`' . $latest_products_orderBy . '` DESC, p.`virtuemart_product_id` DESC';;
break;
case 'random':
$orderBy = ' ORDER BY RAND()'; //LIMIT 0, '.(int)$nbrReturnProducts ; //TODO set limit LIMIT 0, '.(int)$nbrReturnProducts;
break;
case 'topten':
$orderBy = ' ORDER BY p.`product_sales` DESC, p.`virtuemart_product_id` DESC'; //LIMIT 0, '.(int)$nbrReturnProducts;  //TODO set limitLIMIT 0, '.(int)$nbrReturnProducts;
$joinPrice = true;
$where[] = 'pp.`product_price`>"0.001" ';
break;
case 'recent':
$rIds = self::getRecentProductIds($nbrReturnProducts); // get recent viewed from browser session
return $rIds;
}
// $joinCategory = false ; //creates error
// $joinMf = false ; //creates error
$joinPrice = TRUE; //Why we set this all the time?
$this->searchplugin = FALSE;
// $joinLang = false;
}


Thanks in advance for any help and kind regards,
Kevin