Hi
You did good analysis.
Coding changes don't affect traffic, it will increase traffic when you have
quick response from server.
If incase you have changed filename, then traffic will reduce. Say eg. It
was login-track.php then you made changes as login-track-new.php.
Thanks,
Muthukumar Selvarasu,
Project Manager (Web Development),
Webmasters Ltd.
From: php-list@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf
Of Daniel Israel
Sent: Friday, March 07, 2008 8:42 AM
To: php-list@yahoogroups.com
Subject: [php-list] PHP/MySQL Performance question
Greetings listgang!
I have inherited this project and one of the things they do is track logins.
They have a table where the user logs in and they increment a count (if
they've logged in that day) or create a new record.
Our server has been getting KILLED and erroring out a lot. The tech
suggested that the processes might be taking too long. So I did some
profiling and found that when someone has already logged in that day, the
UPDATE to that record take an inordinate amount of time (like 0.5 to 1.5
seconds sometimes). Of course, there's a crapload of records in the table
(and no index either!!). I removed a bunch of old records and it seemed to
speed up (although maybe the traffic just dropped?), so I wanted to see if
this made sense:
function track_login($id, $LoginName, $FullName, $email) {
$db = $this->database;
// see if this user logged in today with this IP address...
$rstLog = $db->query("SELECT LoginID FROM tblLogins WHERE LoginID=" . $id .
" AND DateStamp=CURDATE() AND RemoteAddr='" .
substr($_SERVER["REMOTE_ADDR"], 0, 28) . "'");
if (mysql_num_rows($rstLog) > 0) {
$db->query("UPDATE tblLogins SET Count = Count + 1, LastDateStamp='" .
StrNow() . "' WHERE LoginID=" . $id . " AND DateStamp=CURDATE() AND
RemoteAddr='" . substr($_SERVER["REMOTE_ADDR"], 0, 28) . "'");
}
else {
$db->query("INSERT INTO tblLogins
(LoginID,LoginName,UserName,Email,DateStamp,RemoteAddr,Count,LastDateStamp)
VALUES (" . $id . ", '" . $LoginName . "','" . str_replace("'", "''",
$FullName) . "','" . $email . "',CURDATE(),'" .
substr($_SERVER["REMOTE_ADDR"], 0, 28) . "',1,'" . StrNow() . "')");
}
}
so what I did when I saw the performance increase was add a unique
(auto-increment) ID to the table, and change the method like such:
function track_login($id, $LoginName, $FullName, $email) {
$db = $this->database;
$rstLog = $db->query("SELECT TrackingID, Count FROM tblLogins WHERE
LoginID=" . $id . " AND DateStamp=CURDATE() AND RemoteAddr='" .
substr($_SERVER["REMOTE_ADDR"], 0, 28) . "'");
if (mysql_num_rows($rstLog) > 0) {
list($trackid, $count) = mysql_fetch_array($rstLog);
$db->query("REPLACE INTO tblLogins
(TrackingID,LoginID,LoginName,UserName,Email,DateStamp,RemoteAddr,Count,Last
DateStamp) VALUES (" . $trackid . "," . $id . ", '" . $LoginName . "','" .
str_replace("'", "''", $FullName) . "','" . $email . "',CURDATE(),'" .
substr($_SERVER["REMOTE_ADDR"], 0, 28) . "'," . $count . ",'" . StrNow() .
"')");
}
else {
$db->query("INSERT INTO tblLogins
(LoginID,LoginName,UserName,Email,DateStamp,RemoteAddr,Count,LastDateStamp)
VALUES (" . $id . ", '" . $LoginName . "','" . str_replace("'", "''",
$FullName) . "','" . $email . "',CURDATE(),'" .
substr($_SERVER["REMOTE_ADDR"], 0, 28) . "',1,'" . StrNow() . "')");
}
}
it seems to take care of the problem, but then... could be drop in traffic
(waiting for traffic logs to see)... Does it make sense that this would
work? The REPLACE calls are on the order of 0.01 seconds compared to 0.5
(minimum) before, but I wanted to see if anyone had any experience in this.
(Other solutions or ideas welcome!!)
Thanks!
-D. Israel
[EMAIL PROTECTED] <mailto:dan%40ebji.org>
http://www.customcodebydan.com
AIM: JudoDanIzz
__________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now.
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
