Monday, November 27, 2006

JShop MySQL Query Performance

NOTE: This quick and dirty hack (part of the insert function in /routines/dbAccess_mysql.php) kills the last ID - it would need to be made nice to work 100%.

$theQuery = str_replace('\"', '\\\"', str_replace("'", "''", $sql_query));
if ($result == FALSE) {
$this->lastError = mysql_errno().": ".mysql_error();
mysql_query("insert into amex_dbPerf (descript, err, time) values ('$theQuery', $this->lastError, $totalTime)");
return FALSE;
} else {
mysql_query("insert into amex_dbPerf (descript, err, time) values ('$theQuery', '', $totalTime)");
return $result;

CREATE TABLE `amex_dbperf` (
`actionID` int(11) NOT NULL auto_increment,
`descript` text NOT NULL,
`err` varchar(128) NOT NULL default '',
`time` decimal(12,11) NOT NULL default '0.00000000000',
PRIMARY KEY (`actionID`)
)

PHP Timing

list($secs, $micros) = split(" ", microtime());
$startTime = $secs + $micros;
//Do Something
list($secs, $micros) = split(" ", microtime());
$totalTime = ($secs + $micros) - $startTime;
totalTime is a decimal with seconds