Site slowdown, elminating possibilities.
| From: | Mike Morton | Date: | Tue, 06 Jan 2004 19:59:31 +0000 |
| Subject: | Site slowdown, elminating possibilities. | ||
| Groups: | php.general | ||
| Request: | Send a blank email to php-general+get-173898@lists.php.net to get a copy of this message | ||
Hey everybody. I am trying to eliminate possibilities for a site that is
slowing down, and was hoping that you all could pick apart some code for me
to see if there is some optimizations that you may suggest that may make
some significant difference.
The code in question here is called EVERY page to keep track of a users
activity status. It is code I have used before elsewhere, without problems,
but as I said, I am trying to eliminate possible problems.
Without Further Ado:
//this is the sessions file that records actions and movement of the
customer
$c=@mysql_connect("localhost","<removed>","<removed>");
@mysql_select_db("tienda");
session_start();
$SID_VAR=explode("=",SID);
if(isset($SID_VAR[1])) { $PHPSESSID=$SID_VAR[1]; }
if($PHPSESSID) {
$res=@mysql_query("select * from orders where
session_id='$PHPSESSID'",$c);
$row=@mysql_fetch_array($res);
//if it exists - update the last_active field. if not - create it.
if(strlen($row[session_id]) > 0) {
$res=@mysql_query("update orders set last_active=now() where
session_id='$PHPSESSID'",$c);
} else {
$res=@mysql_query("insert into orders set
session_id='$PHPSESSID',affiliate_id='$aid',session_start=now(),status='1'",
$c);
}
if(strlen($aid) > 0) {
$res=@mysql_query("update orders set affiliate_id='$aid' where
session_id='$PHPSESSID'",$c);
}
}
//load the sessions now.
$res=@mysql_query("select * from orders where session_id='$PHPSESSID'",$c);
$SESSION_VARS=@mysql_fetch_array($res);
//we are going to do affiliate calculations here - if there is an aid then
we are going
//to consider this a clickthrough.
if($aid) {
//check to see if there is an affiliate by that id first.
$res=mysql_query("select affiliate_id from affiliates where
affiliate_id='$aid'",$c) or die(mysql_error());
$row=mysql_fetch_array($res);
if(strlen($row[0]) > 0) {
$current_date=date("Y-m-d");
//we have an affiliate id - decide if this is an insert or update.
$res=mysql_query("select affiliate_id from affiliate_stats where
affiliate_id='$aid' and stat_date='$current_date'",$c) or
die(mysql_error());
$row=mysql_fetch_array($res);
if(strlen($row[0]) > 0) {
//we are updating
$res=mysql_query("update affiliate_stats set hits=(hits+1) where
affiliate_id='$aid' and stat_date='$current_date'",$c) or
die(mysql_error());
} else {
//we are inserting
$res=mysql_query("insert into affiliate_stats set
hits='1',stat_date='$current_date',affiliate_id='$aid'",$c) or
die(mysql_error());
}
}
}
As I mentioned - nothing strange, odd, or anything that should cause a large
slowdown in the database.
Running PHP 4.3.2, Mysql 3.23.whatever The 'orders' database you see here
is really light, 16K rows only, and the sessions_id field is indexed.
I am seeing a number (50-60) sleeping processes in the mysql server, not
sure how they got there, why, or even if they would affect the speed, but
that has been sent to the mysql list anyhow :)
If anyone has any suggestions, criticisms or witty remarks, they are all
accepted :)
TIA
--
Cheers
Mike Morton
****************************************************
*
* Tel: 905-465-1263
* Email: mike@webtraxx.com
*
****************************************************
"Indeed, it would not be an exaggeration to describe the history of the
computer industry for the past decade as a massive effort to keep up with
Apple."
- Byte Magazine
Given infinite time, 100 monkeys could type out the complete works of
Shakespeare. Win 98 source code? Eight monkeys, five minutes.
-- NullGrey