Posted by Rik on 04/29/06 00:34

Ridge Burner wrote:
> Can someone tell me which of these 2 SQL queries will be more
> efficient? I'm having a debate with another guy about which would be
> less resource intensive for MySQL.
Depends:
<?php
$db = mysql_connect();
$conn = mysql_select_db("testbase",$db);

$start1 = microtime(true);
for($i=1;$i<2000;$i++){
$sql = "SELECT * FROM wnk_huizen ORDER BY RAND() LIMIT 1";
$query = mysql_query($sql);
$result = mysql_fetch_array($query);
}
$end1 = microtime(true);

$start2 = microtime(true);

for($i=1;$i<2000;$i++){
$value = array();
$sql = "SELECT id FROM wnk_huizen";
$query = mysql_query($sql);
while($result = mysql_fetch_array($query)){
$value[] = $result;
}
$random = rand(0,count($value)-1);
$sql = "SELECT * FROM wnk_huizen WHERE id='" . $value[$random] . "' LIMIT
1";
$query = mysql_query($sql);
$result = mysql_fetch_array($query);
}
$end2 = microtime(true);
echo "<br />result with ".count($value)." records;<br />";
$time1= $end1-$start1;
$time2= $end2-$start2;
echo "<br />Option 1:".$start1."-".$end1." :".$time1;
echo "<br />Option 2:".$start2."-".$end2." :".$time2;
?>

Result from my extremely slow testserver:

result with 69 records;

Option 1:1146258591.25-1146258626.6845 :35.434526205063
Option 2:1146258626.6845-1146258636.4802 :9.7956740856171

result with 2208 records;

Option 1:1146259157.8281-1146259527.2195 :369.39138197899
Option 2:1146259527.2195-1146259655.4201 :128.20057296753

You'd think option 1 is slower, except if I use a different table:

result with 613 records (id=int(4), primary key);

Option 1:1146258636.4804-1146258644.8055 :8.3251550197601
Option 2:1146258644.8056-1146258683.6749 :38.869340896606

I presume it's because the first table had "text" fields, the second
integers and a 2 VARCHAR(50).
for your reference:
TABLE 1:
Field Type Null Key Default Extra
id int(5) PRI NULL auto_increment
stad varchar(50)
postcode varchar(7)
adres varchar(70)
page varchar(40)
prijs varchar(20)
short_desc text
long_desc text
status char(1)
time timestamp YES CURRENT_TIMESTAMP

TABLE 2
Field Type Null Key Default Extra
id int(5) PRI NULL auto_increment
img_name varchar(50)
img_huis_id int(5) 0
img_huis_default tinyint(1) 0
img_huis_desc varchar(50)


So, it's highly dependable on the database, I'm not going to waste time
checking the details myself, but some people on the mysql newsgroup might
now how ORDER BY RAND() is affected by type of fields in a table.

Grtz,
--
Rik Wasmus

[Back to original message]