Fixing Speed Issues in PHP/MySql

There are 70% of the cases with PHP/Mysql applications where speed is slow because of bad sql queries. To know if you’re not doing anything wrong, here’s the procedure.

You have to record the time before executing an SQL query.

< ?php
$time = microtime();
$time = explode(’ ‘, $time);
$time = $time[1] + $time[0];
$starttime = $time;
?>


Now, add this immediately after mysql_fetch_query function.

< ?php
$time = microtime();
$time = explode(” “, $time);
$time = $time[1] + $time[0];
$endtime = $time;
$totaltime = ($endtime - $starttime);
echo ‘SQL Query executed in ‘ .$totaltime. ‘ seconds.’;
?>

To review this thing, if you are having a code like:

mysql_connect(”localhost”, “mysql_user”, “mysql_password”) or
die(”Could not connect: ” . mysql_error());
mysql_select_db(”mydb”);
$result = mysql_query(”SELECT id, name FROM mytable”);
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
printf(”ID: %s Name: %s”, $row[0], $row[1]);
}

mysql_free_result($result);
?>

Then, your final code must be:

mysql_connect(”localhost”, “mysql_user”, “mysql_password”) or
die(”Could not connect: ” . mysql_error());
mysql_select_db(”mydb”);
// code to initiate our time
$time = microtime();
$time = explode(’ ‘, $time);
$time = $time[1] + $time[0];
$starttime = $time;
$result = mysql_query(”SELECT id, name FROM mytable”);
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
printf(”ID: %s Name: %s”, $row[0], $row[1]);
}
// lets check the time gap
$time = microtime();
$time = explode(” “, $time);
$time = $time[1] + $time[0];
$endtime = $time;
$totaltime = ($endtime - $starttime);
echo ‘SQL Query executed in ‘ .$totaltime. ‘ seconds.’;
mysql_free_result($result);
?>


This code is not complete in itself, but enchancing it more can help a lot in projects.