alternative to order by rand()

Submitted by atman on Thu, 2011-01-06 03:09 in Price Tapestry

david,

is there a way to totally remove the order by rand() function and use another less resource hungry fucntion to show random products?

regards,

alex

Hi Alex, Yes - see the

Submitted by support on Thu, 2011-01-06 10:11

Hi Alex,

Yes - see the latest post regarding random featured products which includes an additional modification to pick a random merchant, followed by random products from that merchant which is far less resource intensive than ordering by RAND() against the entire products table...

Cheers,
David.
--
PriceTapestry.com

Hi David, just tried the

Submitted by Perce2 on Thu, 2011-01-06 11:00

Hi David, just tried the code suggested above and it results in displaying no products. This is the code (copied from your link);

$sql = "SELECT merchant FROM `".$config_databaseTablePrefix."feeds` ORDER BY RAND() LIMIT 1";
database_querySelect($sql,$result);
$merchant = $result[0]["merchant"];
$sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` WHERE merchant='".database_safe($merchant)."' ORDER BY RAND() LIMIT 3";

Hi, Ah - with the latest

Submitted by support on Thu, 2011-01-06 11:02

Hi,

Ah - with the latest distribution it should be selecting merchant against the products table rather than the feeds table - try the following:

$sql = "SELECT merchant FROM `".$config_databaseTablePrefix."products` ORDER BY RAND() LIMIT 1";

database_querySelect($sql,$result);

$merchant = $result[0]["merchant"];

$sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` WHERE merchant='".database_safe($merchant)."' ORDER BY RAND() LIMIT 3";

Cheers,
David.
--
PriceTapestry.com

hi david. i updated the

Submitted by atman on Thu, 2011-01-06 16:35

hi david.

i updated the sites to use the modification you gave a few months back but hosts are still complaining about the order by rand function.

Is there any way to show random feature with very minimal server resource use ?

I have disabled all related products on all sites for now.

Thanks

Hi Alex, Are your sites

Submitted by support on Thu, 2011-01-06 17:01

Hi Alex,

Are your sites using an import @ALL process; which would ensure that the ID field on the products table is contiguous?

If that's the case, it would be MUCH more efficient to chose random integers between 1 and MAX(id) and then SELECT those products directly. Have a go with something like this:

$sql = "SELECT MAX(id) as maxId FROM `".$config_databaseTablePrefix."products`";

database_querySelect($sql,$result);

$maxId = $result[0]["maxId"];

$ins = array();

do {

  $randId = rand(1,$maxId);

  $ins[$randId] = "'".$randId."'";

} while(count($ins) < 3);

$in = implode(",",$ins);

$sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` WHERE id IN (".$in.")";

Cheers,
David.
--
PriceTapestry.com