Sane

Update is one of the slowest MySQL queries. That in conjunction with repeatedly calling the mysql function "curtime()" not only once, but twice, for every updated row... could be causing a problem.

Are you sure that this is causing the bulk of the lag? I wouldn't change the query until you have tested to see that this is 100% true. Once you are sure, you could make that query faster.

If it's only supposed to run when you're logged in you've obviously not got your flags working correctly. Output the flag you're using, look into what's going wrong. That whole kit'n'kaboodle.

FireflyX

Yes i'm now 100% sure because when i think back it was about the time i'd created that active users list that these problems started happening. I also performed a test to see on which line the maximum execution time was being exceeeded and it come up with line 149 which is where that query is. Now does anyone have any suggestions of how i can do a faster query?

Besides that the only other problem that seems to be happening is this:

Warning: mysql_connect() [function.mysql-connect]: Too many connections in e:\domains\s\staticfire.co.uk\user\htdocs\v2\mysql\mysql_connect.php on line 7

yet i close each connection with mysql_close in the page footer.

Sane

For the "too many connections" problem, how are you opening and closing everything? It should be opened once in your header, and closed in your footer. If you have to connect in more than one place... just ... because (?) and it's just not matching up, try printing something beside your connect call, and something else beside your close call. See how they line up.

To make that query faster:

- Is "loginname" the primary key? It will go faster if it you use the primary key as your column to compare. If a user ID is your primary key, use that instead. That way it only needs to keep looking until it finds one entry, instead of searching the entire table every time. MySQL also knows how to find the primary key faster than other columns.

- Make sure you don't call the mysql query if $loginname is NULL or empty. That's unecessary.

- I don't think you should use curtime() unless you really have to. Use your PHP to handle all time fetching, conversions, and representations. Just my opinion. But it won't make any noticable difference in the speed if it's only that one column being updated.

FireflyX

I am opening the connection in my banner code and then closing it in the page footer. So you suggest that i set loginname to the primary key and that should increase speed? I'll give that a try anyway.

Sane

You should see a pretty big-speed up (especially if the table's large), but if that doesn't help, I can't see anything else peculiar with the query. So I'd experiment running different queries in complete isolation, and see if you can find a pattern in what's slower than others. Good luck.

Harakim

"UPDATE active SET time=CURTIME(),timeout=ADDTIME(CURTIME(), '00:10:00') WHERE loginname='$loginname'"

I've never programmed in php, but do you need to escape some of those quotes, etc?

Sane

You don't escape single quotes in a double-quoted string, for any programming language that I know of...

And if you've read any of the posts in this thread, you'll see that the code obviously works...