Dynamis, I share califdon's sentiments. Thanks for taking an experimental approach to something that's too often approached on the basis of hearsay. It's in that spirit of being precise and objective that I'd like to harp on again about the query cache.
Two timings being nearly identical don't imply that the query cache is on. Whether the query cache is on is determined by the state of a couple of system variables, and whether a given query came out of the cache can be inferred from the change in state of a couple of status variables. I reworked your test code to do those checks, see below.
My reading of the documentation is that query caching is
on by default, with a limit of
1M.
The 1M limit for the query cache may be significant for the case with a column length of 25 and 2500 rows. It means that the results for no more than 16 columns can fit in the cache (16 * 25 * 2500=1000000). Therefore it's possible that the 1 column case and the 10 column case came from the query cache, but the 26 column case didn't, and in fact that was the case when I ran the test code on my system. So that's a very good reason to only take the columns you're interested in.
Another measure that might be interesting is the number of bytes in the result. Those have to be copied between process spaces, which presumably can be done only so fast, which presumably means that the more columns in the result, the slower to copy. If this was the only thing that determined the timings, one would expect the 26 column query to take 26 times longer than the one-column query. I'm not getting anything like those results: for me it takes around 2.5 times longer.
Test code below, with checks to system status to count query cache hits, etc:
Code: Select all
<?php
/*
* Measuring performance effect of number of cols
* in SELECT query.
* viewtopic.php?f=2&t=85542
*/
ini_set('display_errors', 1);
error_reporting(E_ALL|E_STRICT);
mysql_connect("localhost","root","root") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());
function e($sql){
$result = mysql_query($sql) or die(mysql_error());
return $result;
}
function q($sql){
$result = array();
$resultset = e($sql);
while($row=mysql_fetch_row($resultset)){
$result[]= $row;
}
return $result;
}
function status(){
$result = array();
foreach(q("show status") as $row){
$result[$row[0]]= $row[1];
}
return $result;
}
function vars(){
$result = array();
foreach(q("show variables") as $row){
$result[$row[0]]= $row[1];
}
return $result;
}
define("TABLE", "testtime");
function colname($n){return "f".$n;}
function cols($n){return array_map("colname", range(1, $n));}
function fx($body){return create_function('$x', $body);}
function populate($num_cols, $num_rows, $bytes_per_col){
e("drop table if exists ".TABLE);
e("create table ".TABLE."(".
implode(",\n", array_map(fx("return \$x.' varchar($bytes_per_col)';"),
cols($num_cols))).
")");
$field_values = array_fill(0, $num_cols,
"'".str_pad("", $bytes_per_col, "x")."'");
for($i=0;$i<$num_rows;$i++) {
e("insert into ".TABLE." values (".implode(", ", $field_values).")");
}
}
function t($num_cols, $num_iterations){
$sql = "select ".implode(", ", cols($num_cols))." from ".TABLE;
$total = 0;
for($count = 0;$count<$num_iterations;$count++){
$start_time = gettimeofday(TRUE);
e($sql);
$total+=(gettimeofday(TRUE)-$start_time);
}
return $total/$count;
}
function g($map, $key, $default){
return array_key_exists($key, $map)?$map[$key]:$default;
}
function p(){
$a = func_get_args();
if(php_sapi_name()=="cli"){
echo implode("\t", $a), "\n";
}else{
echo "<tr>";
foreach($a as $td){echo "<td>".$td."</td>";}
echo "</tr>";
}
}
function main(){
if(php_sapi_name()!="cli"){echo "<table border=1>";}
$num_cols = 26;
$col_length = 25;
$num_rows = 2500;
$num_iterations = 20;
$query_cache_type = 'ON'; // set to ON/OFF
p("num cols", $num_cols);
p("col length (bytes)", $col_length);
p("num rows", $num_rows);
populate($num_cols, $num_rows, $col_length);
e("set session query_cache_type = '$query_cache_type'");
$vars = vars();
$start_status = status();
foreach(array(1, 10, $num_cols) as $c){
$timings[$c] = t($c, $num_iterations);
p("avg timing for $c cols(secs):", $timings[$c]);
}
$total_time = array_sum(array_values($timings))*
$num_iterations;
p("$num_cols cols timing/one col timing",
$timings[$num_cols]/$timings[1]);
p("10 cols timing/one col timing", $timings[10]/$timings[1]);
p("$num_cols cols timing/10 col timing",
$timings[$num_cols]/$timings[10]);
$end_status = status();
foreach($end_status as $k=>$v){
if($diff = $end_status[$k] - $start_status[$k]){
$status_diff[$k] = $diff;
}
}
$mb = 1024*1024;
$measured_bytes_sent = g($status_diff, 'Bytes_sent', 0);
$bytes_per_column = $num_rows*$col_length;
foreach(array(
"bytes per column"=>$bytes_per_column,
"bytes per table"=> $bytes_per_column*$num_cols,
"estimated Mb sent"=> $num_iterations*$col_length*
$num_rows*array_sum(array_keys($timings))/$mb,
"measured Mb sent"=> $measured_bytes_sent/$mb,
"total time (secs)"=> $total_time,
"Mb/sec"=>(($measured_bytes_sent/$total_time)/$mb),
"estimated Mb/sec, $num_cols col query"=>((($bytes_per_column*$num_cols)/$timings[$num_cols])/$mb),
"estimated Mb/sec, 10 col query"=>((($bytes_per_column*10)/$timings[10])/$mb),
"estimated Mb/sec, 1 col query"=>((($bytes_per_column)/$timings[1])/$mb),
"query cache type"=>$vars["query_cache_type"],
"query cache limit"=>$vars["query_cache_limit"],
"num cols that could fit in qc (estimated)"=>
$vars["query_cache_limit"]/($num_rows*$col_length),
"query cache hits"=> g($status_diff, 'Qcache_hits', 0),
"num selects"=> g($status_diff, 'Com_select', 0),
) as $k=>$v){
p("$k:", $v);
}
p("");
p("CHANGES IN STATUS VARS:");
foreach($status_diff as $k=>$v){p($k,$v);}
}
main();
?>