asked on

Optimizing a mysql query (random set mt_rand)

I use a query to get random values from DB, which works fine, but the load on the server is quite large.
Can you think of a way optimizing this query?

<?php
$maxRs = mysql_query("select bizID from this_database WHERE bizState ='$bizState' AND bizLive='1'");
$maxRows = mysql_num_rows($maxRs);
$recordNumber = mt_rand(1 , $maxRows );
$rs = mysql_query("select bizName,bizAddr,bizCity,bizState,bizZip,bizPhone from this_database WHERE bizState ='$bizState' AND bizLive='1' LIMIT $recordNumber,1");
$row = mysql_fetch_array($rs, MYSQL_NUM);
?>

Open in new window

Cem Türk

<?
php


$rs 
=
 mysql_query
(
"select bizName,bizAddr,bizCity,bizState,bizZip,bizPhone from this_database WHERE bizState ='$bizState' AND bizLive='1' ORDER BY rand() LIMIT 1"
);

$row 
=
 mysql_fetch_array
(
$rs
,
 MYSQL_NUM
);



?>

Open in new window

seopti

ASKER

Thanks but ORDER BY RAND() is even using 10x MORE server resouces, so this no solution to my problem.

nplib

<?php
$rs = mysql_query("select bizName, bizAddr, bizCity, bizState, bizZip, bizPhone from this_database WHERE bizState ='$bizState' AND bizLive='1' LIMIT $recordNumber,1");
$max = mysql_num_rows($rs);
$int = mt_rand(1, $max);
echo mysql_result($rs, $int, 'bizName');
echo mysql_result($rs, $int, 'bizAddr');
//etc..
?>

Open in new window

Cem Türk

<?
php
$maxRs 
=
 mysql_query
(
"select max(bizID) from this_database WHERE bizState ='$bizState' AND bizLive='1'"
);

$maxID 
=
 mysql_result
(
$maxRs
,0);

$randomID 
=
 mt_rand
(1

 
,
 $maxID 
);

$rs 
=
 mysql_query
(
"select bizName,bizAddr,bizCity,bizState,bizZip,bizPhone from this_database WHERE bizID=" . $randomID
);

$row 
=
 mysql_fetch_array
(
$rs
,
 MYSQL_NUM
);



?>

or


<?
php
$maxRs 
=
 mysql_query
(
"select max(bizID) from this_database WHERE bizState ='$bizState' AND bizLive='1'"
);

$maxID 
=
 mysql_result
(
$maxRs
,0);

$randomID 
=
 mt_rand
(0
 
,
 $maxID 
);

$rs 
=
 mysql_query
(
"select bizName,bizAddr,bizCity,bizState,bizZip,bizPhone from this_database WHERE bizID>" . $randomID
 . " LIMIT 1");

$row 
=
 mysql_fetch_array
(
$rs
,
 MYSQL_NUM
);



?>

Open in new window

Cem Türk

here is another approach

<?php
$maxRs = mysql_query("select bizID from this_database WHERE bizState ='$bizState' AND bizLive='1'");
$maxRows = mysql_num_rows($maxRs);
$recordNumber = mt_rand(1 , $maxRows );
$theID=mysql_result($maxRs,$recordNumber-1);
$rs = mysql_query("select bizName,bizAddr,bizCity,bizState,bizZip,bizPhone from this_database WHERE bizID=" . $theID . " LIMIT 1");
$row = mysql_fetch_array($rs, MYSQL_NUM);
?>

Open in new window

ASKER CERTIFIED SOLUTION

virmaior

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial