Re: Table Locking Memory Engine

[Daniel Oneil]

Here are the lines which querie the one table.

$sql = "SELECT IP FROM $dbname WHERE user = '$tnuser' AND ip = '$User_IP'";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
} else{
$unreadableResult = mysql_query($sql,$con);
}
$readableResult = mysql_fetch_assoc($unreadableResult);
$User_1 = $readableResult['ip'];
$page_1 = $readableResult['page'];
$sql = "Select id from $dbname WHERE user = '$tnuser' AND ip = '$User_IP'";
if (!$result = mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
if(mysql_num_rows($result) > 0){
$sql = "UPDATE $dbname set time = '$timestamp', timeout = '$timeout', page = '$page' WHERE user = '$tnuser' AND ip = '$User_IP'";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
}
else
{
$sql = "INSERT INTO $dbname (user,time,ip,timeout,page,browser,platform,country) VALUES('$tnuser','$timestamp','$User_IP','$timeout','$page','$browser','$platform','$country')";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
}
$sql = "DELETE FROM $dbname where timeout < $timestamp";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
$sqlcnt = "Select count(id) as total from $dbname WHERE user = '$tnuser'";
if (!mysql_query($sqlcnt,$con))
{
die('Error: ' . mysql_error());
}
else
{
$cntUnreadableResult = mysql_query($sqlcnt,$con);
}
$cntReadableResult = mysql_fetch_assoc($cntUnreadableResult);
$online = $cntReadableResult['total'];

The table during peak can reach 20,000+ rows
The uptime at this time is 2 days 16 hours

and phpmyadmin says
Table_locks_waited 17 M

Since its startup, 61,269,328 queries have been sent to the server.

Thanks



Edited 2 time(s). Last edit at 08/09/2011 05:31PM by Daniel Oneil.