I’m trying to evaluate a couple of different ways of pulling data from the database and displaying it using PHP, and want to see which is the most efficient.
To test which is most efficient I’ve put both pieces of code in an array, then run a loop 500 times executing both pieces of code in a random order, and saving the execution time each time into a csv file.
However, I think there must be something wrong with my testing method as the times saved indicate that the SQL query with an extra JOIN is fastest, and the rest of the code for one method is consistently faster than the other even though the code is almost identical between both methods.
Here’s the code:
<?php
include('globalFuncs.inc.php');
$sql = array(array(),array());
$sql[0]['standard'] = "SELECT images_2.id, images_2.Headline, images_2.fileext, images_2.Rating, images_2.url_name,
SUBSTRING_INDEX(imageData.ImageDescription, '\
', 1) AS summary, imageData.GPSLatitude, imageData.GPSLongitude, imageData.GPSAltitude, DATE_FORMAT(imageData.Last_updated,'%Y-%m-%dT%TZ') AS Last_updated,
CONCAT('<category term=\\"', GROUP_CONCAT(keywords.Subject SEPARATOR '\\" />\
<category term=\\"'), '\\" />') AS tags
FROM images_2
LEFT JOIN imageData ON images_2.id = imageData.id
LEFT JOIN img_keywords ON images_2.id = img_keywords.img_id
LEFT JOIN keywords ON keywords.id = img_keywords.keywords_id
WHERE images_2.id IN(SELECT DISTINCT img_categories.img_id
FROM img_categories
LEFT JOIN categories AS node ON node.id = img_categories.categories_id,
categories AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.id = 35)
GROUP BY images_2.id
ORDER BY images_2.id DESC
LIMIT 30,30";
$sql[1]['standard'] = "SELECT images_3.id, images_3.Headline, fileType_2.Ext, images_3.Rating, images_3.url_name,
SUBSTRING_INDEX(imageData.ImageDescription, '\
', 1) AS summary, imageData.GPSLatitude, imageData.GPSLongitude, imageData.GPSAltitude, DATE_FORMAT(imageData.Last_updated,'%Y-%m-%dT%TZ') AS Last_updated,
CONCAT('<category term=\\"', GROUP_CONCAT(keywords.Subject SEPARATOR '\\" />\
<category term=\\"'), '\\" />') AS tags
FROM images_3
LEFT JOIN imageData ON images_3.id = imageData.id
LEFT JOIN img_keywords ON images_3.id = img_keywords.img_id
LEFT JOIN keywords ON keywords.id = img_keywords.keywords_id
LEFT JOIN fileType_2 ON fileType_2.id = images_3.fileext
WHERE images_3.id IN(SELECT DISTINCT img_categories.img_id
FROM img_categories
LEFT JOIN categories AS node ON node.id = img_categories.categories_id,
categories AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.id = 35)
GROUP BY images_3.id
ORDER BY images_3.id DESC
LIMIT 30,30";
$sql[0]['createImgTag'] = function($data){
$str = '';
foreach($data as $img){
$str .= '<img src="'.STATIC2.'/Img/thumbs/'.$img['url_name'].$img['fileext'].'" />';
}
return $str;
};
$sql[1]['createImgTag'] = function($data){
$str = '';
foreach($data as $img){
$str .= '<img src="'.STATIC2.'/Img/thumbs/'.$img['url_name'].$img['Ext'].'" />';
}
return $str;
};
$keys=array(0,1);
$runs=500;
$csv = array();
//Test standard query and creating img tag
for($i=0;$i<$runs;$i++){
shuffle($keys);
foreach($keys as $key){
//Test standard query
$start = microtime(true);
if($result = $conn->query($sql[$key]['standard'])){
$data = array();
while($row = $result->fetch_assoc()){
$data[] = $row;
}
$result->close();
$end = microtime(true);
}
else{echo $conn->error; exit();}
$csv[$i][$key] = number_format($end - $start, 20);
//Test creating img tag
$start = microtime(true);
/*echo*/ $sql[$key]['createImgTag']($data);
$end = microtime(true);
$csv[$i][$key+2] = number_format($end - $start, 20);
//echo '<br />Query for '.$key.' method took '.$csv[$i][$key].'s + creating tage took '.$csv[$i][$key+2].'<br /><br />';
}
}
//Dump data
array_unshift($csv, array('standard 0', 'standard 1', 'createImgTag 0', 'createImgTag 1'));
$file= fopen('./resultsv3.csv', 'w');
foreach($csv as $fields){
fputcsv($file, $fields);
}
fclose($file);
And it’s giving me results like:
"standard 0","standard 1","createImgTag 0","createImgTag 1"
0.02683115005493164062,0.00004792213439941406,0.01996994018554687500,0.00003910064697265625
0.02115297317504882812,0.00003600120544433594,0.01080298423767089844,0.00003600120544433594
Can anyone see anything wrong with my testing method or suggest a better way of comparing efficiency of code snippets / queries?
Thanks
Dave