[thelist] [best practices] Join queries in MySQL?

[Liam Delahunty]

I posted this a while back on another list, while it's a not a loop/join
examples, it did indicate to me that two calls can be better than one.
Though that's possibly more to do with my PHP code than the actual
database... here you go anyway...

>>
I've got the following queries and results are indicating to me that a "SHOW
COLUMNS" then getting the data with a mysql_fetch_row is quicker than just
one "mysql_fetch_assoc".

The results surprised me, and showed that the two calls are significantly
quicker than the one. (Just done quickly on my local windows system). So,
now I'm wondering is there something flawed in my logic here, or am I
getting results that you would expect?

function getmicrotime(){
    list($usec, $sec) = explode(" ",microtime());
    return ((float)$usec + (float)$sec);
    }

$one = getmicrotime();
for ($i=0; $i<10;$i++) {
$query = "SHOW COLUMNS FROM site_rules_tbl ";
$result = mysql_query($query, $connection);
	while ($myrow = mysql_fetch_row($result))
	{
	$cols[] = $myrow[0];
	}

$query = "SELECT * FROM site_rules_tbl";
$result = mysql_query($query, $connection);
	while ($data = mysql_fetch_row($result))
	{
	for ($i=0; $i < count($cols); $i++)
		{
		$colNames = $cols[$i];
		$$colNames = $data[$i];
		}
	}
}

$two = getmicrotime();

for ($i=0; $i<10;$i++) {
$query = "SELECT * FROM site_rules_tbl";
$result = mysql_query($query, $connection);
	while ($myarray = mysql_fetch_assoc($result)) {
		while (list($key,$val) = each($myarray)) {
			$$key = $val;
		}
	}
}
$three = getmicrotime();

$col_result = $two - $one;
$arr_result = $three - $two;
$diff = $col_result - $arr_result;

print ("<p>$one<br>$two<br>$three</p>");
print ("<p>Col: $col_result</p>");
print ("<p>Arr: $arr_result</p>");
print ("<p>Diff: $diff</p>");

Kind regards,
Liam Delahunty
Mega Products Limited, 10-11 Moor Street, Soho, London W1D 5NF
t: 020 7434 4201 f: 0870 135 8412
http://www.liamdelahunty.com/ web/ design/ database/ programming
http://www.britstream.com/ Hosting/ Domain Names From UKP 7.50 p.a.