mysql optimization

nasht

Hi,

I've been learning php on my own.
Today I have to start a new project that might have very high traffic, and I want to make sure my techniques are not too slow.

Here my problem is with MySQL.
What are the best techniques to take the best out of MySQL with php?
At least, logically. Like, when should connections be opened and closed?
What are the best functions to read from the database?

Maybe if you know an article or something...

Meanwhile, here is the function I currently use to connect:

function send_sql($sql,$db) {


$MySQL_Host="localhost";
$MySQL_User="myusername";
$MySQL_Passw="mypassword";



$dblk=mysql_connect($MySQL_Host, $MySQL_User, $MySQL_Passw);

if(!$dblk){
print "erreur connection $dblk<br>";
exit;
}

if(!mysql_select_db($db,$dblk)){
print "erreur ".mysql_error()."<br>";
mysql_close($dblk);
exit;
}

if (! $res=mysql_db_query($db,$sql)) {
echo mysql_error();
exit;
}

mysql_close($dblk);

return $res;
}

And here is an example of a use:

if($res=send_sql("SELECT gender, state, country FROM users WHERE registerdate >= '$from' AND registerdate <= '$to' ORDER BY registerdate DESC","mmh")){
	$count=mysql_num_rows($res);
		while($ans=mysql_fetch_row($res)){
		$genders[$ans[0]] += 1;
		$states[$ans[1]] += 1;
		$countries[$ans[2]] += 1;

	//$users[gender][] = $ans[0];
	//$users[state][] = $ans[1];
	//$users[country][] = $ans[2];
	}
}

Any comments? Suggestions?

Thanks You!

Nathan

tomhath

Two thoughts:

1) Pay more attention to the SQL than php. Use "explain" to ensure the most frequently run queries are most optimized (indexes, etc).

2) If things start to get slow, add hardware (memory, cpu's, raid arrays of disks, etc). That's usually cheaper and faster than trying to optimize software that already works.