I ran some simple tests: same complex query in mysql and php and measured the times taken to process them. The results are very controversial and suggest that some queries PHP processes faster than MySql.
In theory, shouldn't be the MySql faster? I am want compare how PHP (scripting language) interface decreases the performance of complex SQL query. But why my results suggest otherwise?
Those are averages, I run each query 3 times!
php code:
<?php
$con = mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("test3", $con);
$start = microtime(TRUE);
$result = mysql_query("SELECT * FROM `test`
ORDER BY `test`.`test1` DESC
LIMIT 0,100");
echo "<table border='1'>
<tr>
<th>ID</th>
<th>test1</th>
<th>test2</th>
<th>test3</th>
<th>test4</th>
</tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['ID'] . "</td>";
echo "<td>" . $row['Test1'] . "</td>";
echo "<td>" . $row['Test2'] . "</td>";
echo "<td>" . $row['Test3'] . "</td>";
echo "<td>" . $row['Test4'] . "</td>";
echo "</tr>";
}
echo "</table>";
$end = microtime(TRUE);
$sqlTime = $end - $start;
mysql_close($con);
echo $sqlTime
?>
The results: time in seconds against the number of records: