Misleading database query statistics

[Veronica]

In debug mode all the time measurements for db query times are misleading
as all the precision is removed before calculating the sum of the individual db query times.

A db query time of up to 0.499 ms is counted as 0 ms and if you have 100 such queries
the total time in Coppermine will be reported as 0 ms even if the real time used is 49.9 ms.

Db query times between 0.5 and 1.49 ms are all reported as 1 ms etc

At my shared web hosting most db query times are less than 0.5 ms.

Also the time to connect to the database is not included in the db query time
and my typical time for a db connect is 2.5 ms

[Αndré]

Suggested patch file attached. I haven't added the database connection time, as the header is "Queries" and my system returned a huge negative value.

[Veronica]

This looks good you add MySQL times in microseconds and publish results in milliseconds (ms) with 2 decimals.

For the question about database connection time its overhead you get for the queries
without a connection you can't execute any queries so time to connect and select a database must be included.

Test this replacement for cpg_db_connect:

function cpg_db_connect()
{
    global $CONFIG, $query_stats, $queries;

    $query_start = cpgGetMicroTime();
    $result = @mysql_connect($CONFIG['dbserver'], $CONFIG['dbuser'], $CONFIG['dbpass']);

    if (!$result) {
        return false;
    }
    $query_end = cpgGetMicroTime();
    if (!isset($CONFIG['debug_mode']) || $CONFIG['debug_mode'] == 1 || $CONFIG['debug_mode'] == 2) {
        $trace = debug_backtrace();
        $last = $trace[0];
        $localfile = str_replace(realpath(dirname(__FILE__) . DIRECTORY_SEPARATOR . '..') . DIRECTORY_SEPARATOR , '', $last['file']);

        $duration      = ($query_end - $query_start) * 1000;
        $query_stats[] = $duration;
        $queries[]     = "mysql_connect: {$CONFIG['dbserver']} [$localfile:{$last['line']}] (".round($duration, 2)." ms)";
    }

    $query_start = cpgGetMicroTime();
    if (!mysql_select_db($CONFIG['dbname'])) {
        return false;
    }
    $query_end = cpgGetMicroTime();
    if (!isset($CONFIG['debug_mode']) || $CONFIG['debug_mode'] == 1 || $CONFIG['debug_mode'] == 2) {
        $duration      = ($query_end - $query_start) * 1000;
        $query_stats[] = $duration;
        $queries[]     = "mysql_select_db: {$CONFIG['dbname']} [$localfile:{$last['line']}] (".round($duration, 2)." ms)";
    }

    if (!empty($CONFIG['dbcharset'])) {
        cpg_db_query("SET NAMES '{$CONFIG['dbcharset']}'", $result);
    }

    return $result;
}

[Αndré]

without a connection you can't execute any queries so time to connect and select a database must be included.

I don't see a reason why it must be included. It could be included, but I don't see a reason to include it. The heading is "Queries", as we want to list the database queries in case somebody wants to find slow queries or for whatever reason. Of course we could add a new section "database connection" or something like that above the queries which shows the information you request. But what's the benefit of this information? We just connect to/select the database once per page load and the average user doesn't need that value nor can fix it himself, usually. If there's something wrong with your MySQL server it's not Coppermine related and a small script will return the same worse value. The debug output isn't meant to provide basic system info.

[Αndré]

Committed my first suggestion in SVN revision 8537.